SQL Server Performance

UPDATE trigger where two tables are updated

Discussion in 'SQL Server 2005 General Developer Questions' started by OffDaBench, Feb 17, 2010.

  1. OffDaBench New Member

    I had a search through previous posts but couldn't find an answer for this.
    I am creating an UPDATE trigger that writes two records (DELETED and INSERTED records) to an audit database when TABLE A is updated. However, the part that I am finding tricky is this:
    TABLE A is closely associated to TABLE B. (Sometimes one table will be updated; sometimes the other table will be updated; sometimes both tables will be updated). So the table in my audit database has fields pulled from both TABLE A and TABLE B. As well as referencing both the DELETED and INSERTED records from TABLE A in my trigger I want to reference both the DELETED and INSERTED records from TABLE B.
    Does anybody have any suggestions how I could do this with one trigger and preferably with just two records (rather than four) added to the table in my audit databse?
    Thanks in advance.
  2. FrankKalis Moderator

    Welcome to the forum!
    What you describe is not possible. The trigger on table A is not able to see and access in inserted & deleted pseudotables that are used by the trigger on table B. You would have to expand the trigger logic to check first if audit rows for the given key already exists, and than updaet them rather than just insert them.
  3. dineshasanka Moderator

    You won't be able to access Table B data from Trigger in Table A. Why you can't wrie two triggers to two tables.
  4. Adriaan New Member

    Agreed, you can't do this in triggers, so you'll need to handle this in your stored procedure(s).
    From SQL 2005, you can add an OUTPUT clause to UPDATE, DELETE and INSERT queries, which will dump the data from the pseudotables into a temp table or table variable, for further use within your procedure.
  5. OffDaBench New Member

    Thank you for the welcome and for each of your replies.
    I think I will initially go for the two stored procedures with improved logic.
    Much appreciated!

Share This Page