• Author says:

    What the INSTEAD OF trigger does is override the normal insert,delete or update statement and leave that responsibility to your Trigger. You will be responsible for inserting, deleting or updating the data to the database.

    Not always! update on blob fields (text, ntext or image) will not fire triggers (This is applicable to both instead of and after triggers)

    Additionally, as the standard statements are overwritten by instead of trigger, it is the responsibility of the trigger to find what fields are being updated.  If the table is modified through ad-hoc queries the trigger can't assume that all fields will be updated always.  If you assume, it will replace the fields  not updated by null.

    UPDATE Table_Name

     SET field1= ISNULL(i.field1, Table_name.field1),

            'Put Here all the fields of the table

            last_modified=getdate()  

     FROM inserted i

     WHERE i.id= Table_Name.id

    Should we consider the performance?  I think not.   We all know that triggers (both types) add significant overhead to the server.

    So is there a better solution?

    Always update the table through stored procedures.  Within the stored procedure, use the update last_modified field. Update through trigger is the last resort. Using stored procedure always has many added advantages too.  (Including security, performance and maintainability)

     

    Cheers,
    Prithiviraj Kulasingham

    http://preethiviraj.blogspot.com/