two triggers on insert of same table

Question

Here is one very interesting problem. I am using SQL Server 2008. I have two triggers on one common table say 'CommonTable'. one trigger is on update and other one is on insert/update/delete.

• In first trigger "Trigger1", I do the checks/rollback sometime change the new inserted value based on business logic. here is sample code

-

CREATE TRIGGER [dbo].[Trigger1] ON [dbo].[CommonTable]
FOR UPDATE
UPDATE [CommonTable] 
SET 
    [StatusCode] = 'New Value'
WHERE 
[RecId] = 'rec id value'

• In second trigger "Trigger2", I store the new inserted/deleted/updated value from 'CommonTable' table to another table 'CommonTable_History' for history tracking purpose. here is sample code

-

CREATE TRIGGER [dbo].[Trigger2] ON [dbo].[CommonTable]
FOR INSERT, UPDATE, DELETE

--based on logic read the value from DELETED or INSERTED table and store in other table.

SELECT @RowData = (SELECT * FROM DELETED AS [CommonTable] WHERE [RecId] = @RowRecId FOR XML AUTO,                       BINARY BASE64 , ELEMENTS)

--and then insert @RowData in 'CommonTable_History' table.

With the help of 'sp_settriggerorder', I have set the order of execution of these triggers, so first "Trigger1" get executed and then "Trigger2".

Second trigger "Trigger2" works well for insert/delete values. It works fine for new inserted value if new inserted values has not been changed by first trigger "Trigger1".

But if in some cases, inserted values has been changed in "Trigger1". say [StatusCode] = 'New Value' and old values was 'Old Value' then "Trigger2" still store the 'Old Value' instead of 'New Value'. Why because "Trigger1" change the value but that value still has not been store in database and before that "Trigger2" get executed on Insert. Now my requirement is, here I want to store "New Value".

So I thought, lets make "Trigger2" to use "AFTER" keywords. But "FOR" and "AFTER" behave same could not solve the problem.

Then I thought, lets make "Trigger2" to use "INSTEAD OF" keyword. But "INSTEAD OF" gives following error "Cannot CREATE INSTEAD OF DELETE or INSTEAD OF UPDATE TRIGGER. This is because the table has a FOREIGN KEY with cascading DELETE or UPDATE."

I can not remove FOREIGN KEY with cascading DELETE or UPDATE for table 'CommonTable'.

Please let me know if you people have any other alternate solution. -Vikram Gehlot

Answer 1

It may not be the cleanest solution but can you simply combine the two triggers into one? That way both pieces of SQL would know about each other's changes.

Answer 2

Your second trigger appears to me as if it would not work properly is mulitple records are inserted in a set-based operations unloess you use a loop which is poor choice in a trigger. Fix that first!

Instead of select * from deleted, why not join the deleted or inserted table to the original table and take the values from there (except for the id value which you get from deleted or inserted, that should give you the most current values of all fileds and if you add other trigger logic later wil not break.

Answer 3

I think your second trigger needs to use the values from the actual table, not the inserted/deleted tables to populate the log table - inserted/deleted will always have the unaltered, original values, while your altered values will appear in the table. Make the second trigger an "After" trigger, so you will not have to use the sp_settriggerorder. Like this, for example:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[trg_Trig1] 
   ON  [dbo].[TestTable] 
   FOR INSERT
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    update TestTable
    set [value] = 10
    where [value] = 25

END

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[trg_Trig2]
   ON  [dbo].[TestTable] 
   AFTER INSERT
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for trigger here
    insert into log_TestTable
    (id, description, [value])
    select tt.id, tt.description, tt.[value]
    from inserted i
        LEFT JOIN TestTable tt
            ON tt.id = i.id

END