generic update this record trigger

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

  • 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

  • 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

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

  • 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

     

  • 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