CODEWITHSUNDEEP
sql-serversql-server-2008triggers
tardomatic
tardomatic

Reputation: 2436

Will an FOR INSERT trigger fire after an INSTEAD OF INSERT trigger has performed an insert?

The title pretty much says it all... I have a table with an INSTEAD OF INSERT trigger which checks certain things and does the actual insert if things are OK. It also has an FOR INSERT, UPDATE trigger which updates values in other tables based on the values in the newly inserted row.

I suspect that the FOR INSERT, UPDATE trigger is not firing. Why would this be?

Upvotes: 0

Views: 2167

Answers (2)

RB.
RB.

Reputation: 37202

which updates values in other tables based on the values in the newly inserted row.

The "inserted" table in the FOR INSERT trigger will contain the values inserted by your INSTEAD OF trigger, NOT your insert statement. For example, consider the following script:

CREATE TABLE Test (
    id int IDENTITY NOT NULL,
    value varchar(20) NOT NULL,
    forTriggerValue char(1)  NULL
)
GO

CREATE TRIGGER forTrigger
ON Test
AFTER UPDATE 
AS 
    IF EXISTS (SELECT * FROM inserted WHERE value = 'MyNewValue')
    BEGIN
        UPDATE Test SET 
            forTriggerValue = 'A'
        FROM    inserted
        WHERE   Test.id IN (SELECT id FROM inserted)
    END
    ELSE IF EXISTS (SELECT * FROM inserted WHERE value = 'InsteadOfValue')
    BEGIN
        UPDATE Test SET 
            forTriggerValue = 'B'
        FROM    inserted
        WHERE   Test.id IN (SELECT id FROM inserted)
    END
    ELSE 
    BEGIN
        UPDATE Test SET 
            forTriggerValue = 'C'
        FROM    inserted
        WHERE   Test.id IN (SELECT id FROM inserted)
    END
GO

CREATE TRIGGER insteadOfTrigger
ON Test
INSTEAD OF  UPDATE 
AS 
    UPDATE Test SET 
        value = 'InsteadOfValue'
    FROM    inserted
    WHERE   Test.id IN (SELECT id FROM inserted)
GO

INSERT INTO Test (value) VALUES ('MyValue')
GO
UPDATE Test SET value = 'MyNewValue' WHERE value = 'MyValue'
GO

SELECT * FROM Test
GO

Your "forTriggerValue" will be 'B', not 'A'.

Upvotes: 3

Joe Stefanelli
Joe Stefanelli

Reputation: 135858

Check the "Allow Triggers to Fire Others" setting on the server. In SSMS, right click on the Server and choose Properties. Then look at the Advanced tab.

enter image description here

Upvotes: 1

Related Questions