April 21, 2011 at 3:50 am
Hi,
Have a table with 275 rows to which I've added a new bit column called IsEnabled. When a delete is issued
on this table I want to intercept this and set this new column to 0.
I've been looking at the "instead of" trigger but I'm not sure how to construct the where clause so that
the update only affects the rows which had been market for delete. The code below, without any where
clause obviously updates all rows, but how do I make it only affect specific rows referenced in the delete?
CREATE TRIGGER deleteCountry ON dbo.COUNTRY
INSTEAD OF DELETE AS
UPDATE dbo.COUNTRY SET IsEnabled=0
DELETE FROM dbo.COUNTRY WHERE COUNTRY_CODE = 'au' -- should only delete one row
Regards
Dave
April 21, 2011 at 4:12 am
In a delete trigger you have a 'deleted' table containing the rows to be/have been deleted so you can use this to update ie
UPDATE c
SET c.IsEnabled=0
FROM dbo.COUNTRY c
JOIN deleted d ON d.COUNTRY_CODE=c.COUNTRY_CODE
However I think this is not quite what you are after as I do not understand by what you mean by 'delete only one row'.
Please post DDL create statement for the table, test data and expected results.
Far away is close at hand in the images of elsewhere.
Anon.
April 21, 2011 at 5:20 am
Hi,
No that is the answer. I just didn't explain in my initial post that the
the where clause of the delete would only affect one row.
ALTER TRIGGER deleteCountry ON dbo.COUNTRY
INSTEAD OF DELETE AS
UPDATE dbo.COUNTRY SET IsEnabled=0
FROM country C INNER JOIN DELETED D ON c.country_code = d.country_code;
DELETE FROM dbo.COUNTRY WHERE COUNTRY_CODE = 'au'
Thanks
Dave
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply