generic update this record trigger

Question

generic update this record trigger

Oblio Leitch

SSCrazy

Points: 2137
More actions
October 8, 2004 at 3:57 pm

#85622

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'?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Razvan Socol SSCarpal Tunnel Points: 4811 More actions · Answer 1

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

Atiq Rahman SSC Journeyman Points: 98 More actions · Answer 2

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

Oblio Leitch SSCrazy Points: 2137 More actions · Answer 3

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?

Atiq Rahman SSC Journeyman Points: 98 More actions · Answer 4

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

Eric Wilson SSChasing Mays Points: 611 More actions · Answer 5

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.)