October 27, 2003 at 5:45 am
know that SQL Server 2000 does not directly support row-level triggers explicitly, however is a way to fire a trigger when particular rows of a table are updated using other SQLServer functionality? Any help would be appreciated.
Patrick Quinn
-------------------------
Patrick Quinn
TRMS Database Administrator
Anteon Corporation
09641-83-7722, DSN 475-7722
Patrick Quinn
October 27, 2003 at 8:40 am
One way could be to add a trigger to the table and check inside if it is 'the row' that has been changed.
Another way is to only allow changes to the table through a stored procedure and catch the update inside that.
October 28, 2003 at 4:44 am
In some cases a cursor is also appropriate - one case would be when you need to call a proc or whatever once for each row modified. Use with care, the overhead of cursor plus proc call (or whatever) can add up.
Andy
October 28, 2003 at 9:43 am
In the update trigger, use the inserted and updated "tables" to compare the record values before and after - they might not actually change. An identity id is handy to link the before to the after records. Perhaps a flag can be used to determine which records to "fire"?
INSERT INTO SpecialAudit
SET RecordId = i.Id,
Who = i.UserId, ...
FROM inserted i
INNER JOIN deleted d ON
d.Id = i.Id
WHERE i.audit = 1 and (
d.col1 <> i.col1 or
d.col2 <> c.col2 ...
I assume no nulls. Perhaps a BINARY_CHECKSUM can be used to detect records that change rather than checking the columns.
Randy
Helpdesk: Perhaps Im not the only one that does not know what you are doing. 😉
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply