October 8, 2004 at 3:57 pm
Can someone help me write a generic trigger that will update the record being updated to change a 'datemodified' column? If it's a 'for update', with the act of 'update table set datemodified = getdate()' cause the trigger to fire again? How do I say 'this record' - does it have to be 'where table.id_col=updated.id_col'?
October 9, 2004 at 12:39 am
The trigger will not be fired again, unless the RECURSIVE_TRIGGERS database option is set (by default it's not). There is no updated pseudo-table, there are only the inserted and deleted pseudo-tables. The trigger may look like this:
CREATE TRIGGER SetDateModifiedForYourTable ON YourTable FOR UPDATE AS SET NOCOUNT ON UPDATE YourTable SET DateModified=GETDATE() WHERE YourPK IN (SELECT YourPK FROM inserted)
Razvan
October 11, 2004 at 1:19 am
Hi,
The other way of doing it is as follows (without using the sub query):
create trigger <Trigger Name> on <Table Name>
for update
as
set nocount on
update <Table Name> set <Column Name> = getdate()
from <Table Name> a join INSERTED b on a.PK = b.PK
Regards,
Atiq Rahman
October 11, 2004 at 8:22 am
I'd love to make this as generic as possible. Is there a way to simply reference something like WHERE lookupPK() = inserted.lookupPK()? So it can just be added to a bunch of tables without needing to know what the PK for that table is?
October 13, 2004 at 5:34 am
Hi,
Yes you can do that, by querying the system tables to get the PK columns for the given table and buield a dynamic statement.
Regards,
Atiq Rahman
November 2, 2004 at 2:18 pm
Dynamic SQL anywhere -- especially triggers -- is less than optimal. Rather than make a server-side trigger generic, I would suggest writing a "code-generator" script that will generate a "strongly typed" correct trigger on demand.
(Or check out something like CodeSmith, or other tools for generating code.)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply