November 1, 2013 at 3:45 am
I have this table:
CREATE TABLE [dbo].[Location](
[LocationID] [int] IDENTITY(1,1) NOT NULL,
[Location] [nvarchar](255) NOT NULL,
[Product_Code] [nvarchar](255) NOT NULL UNIQUE,
[LocationProductStockAmt] decimal(18,2),
CONSTRAINT pk_Location_ID PRIMARY KEY (LocationID),
CONSTRAINT fk_Location_ProductCode FOREIGN KEY (Product_Code) REFERENCES Product(Product_Code),
) ON [PRIMARY]
I created this dummy data:
INSERT INTO LOCATION VALUES ('Johannesburg', '1231', 4000)
INSERT INTO LOCATION VALUES ('Pretoria', '1232', 7236)
INSERT INTO LOCATION VALUES ('East London', '1233', 1256)
INSERT INTO LOCATION VALUES ('Cape Town', '1234', 1258)
INSERT INTO LOCATION VALUES ('Bloemfontein', '1235', 1458)
INSERT INTO LOCATION VALUES ('Rustenburg', '1236', 2145)
INSERT INTO LOCATION VALUES ('Cape Town', '1237', 1236)
INSERT INTO LOCATION VALUES ('Polokwane', '12310',1256)
INSERT INTO LOCATION VALUES ('Kimberly', '1238', 1256)
INSERT INTO LOCATION VALUES ('Welkom', '1239', 1235)
Now I need to create a database object that When a record or records are added or altered by whatever means to LOCATION and the record exists already, based on the "Code" field then the record should be updated with a suffix of " - Updated", if the record does not exist insert the new record
Help please.
November 1, 2013 at 6:55 am
Instead of updating the record's product_code field, why not just add a field that tracks when the row was added and another one for the last update?
The issues I see with updating the product_code field with '-Updated' are similar to the ones in your other post at http://qa.sqlservercentral.com/Forums/Topic1510556-391-1.aspx.
1. If the update is run more than once, it'll contain '-Updated-Updated' on the end of product_code.
2. The length of product_code is 255 and the value will eventually become too long for the field, not even thinking about the value of ProductID. You would need to substring the whole thing.
I would seriously consider using date fields to track insert and update dates or some sort of status field to track the status of the row. You're going to run into problems if you track these things in a string field.
November 1, 2013 at 7:00 am
You can use the MERGE statement.
MERGE your_table_target AS TGT
USING ( SELECT Code,'column1','column2','column3' FROM your_table_source) AS SRC
ON
TGT.Code=SRC.Code
WHEN MATCHED THEN
UPDATE
SET
TGT.updated_column= TGT.updated_column + " - Updated"
WHEN NOT MATCHED THEN
INSERT
(SRC.Code,'column1','column2','column3');
And, As Mr. Ed Wagner stated,
If the update is run more than once, it'll contain '-Updated-Updated' on the end of updated_column.
That is to be handled.
November 1, 2013 at 7:11 am
Please create your table in a dev db
and try this trigger out... Should get you what you need.
hth,
..bob
CREATE TRIGGER LocationAddUpdate
ON Location
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
if update(location)
begin
update LOCATION
SET location = rtrim(Location) + ' Updated'
where LocationID = (select LocationID from Inserted)
end
END
GO
November 1, 2013 at 7:13 am
After reading SSCrazy's post..... I agree with him.
A tracking table will be your best bet.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply