CODEWITHSUNDEEP
sqlsql-servert-sqltriggers
zunetastic
zunetastic

Reputation: 305

INSTEAD OF TRIGGER, Would it infinitely loop?

Would an Insert Statement on a table that has an "INSTEAD OF" trigger cause an infinite "instead, insert" loop of executions?

For example this: 

CREATE TRIGGER setDescToUpper ON part_numbers
INSTEAD OF INSERT

AS
BEGIN
    INSERT INTO part_numbers (
        colA,
        colB,
        part_description
    ) SELECT
        colA,
        colB,
        UPPER(part_description)
    ) FROM
        INSERTED
END
GO

Would the insert statement inside the "instead of" trigger cause a loop?

I dont wanna disable recursive triggers.

Do I need to temporarily disable the trigger?

Source: SQL Server - after insert trigger - update another column in the same table

Upvotes: 17

Views: 3692

Answers (1)

Patrick
Patrick

Reputation: 5846

This INSERT will not re-invoke the trigger.

SQL Server will not call INSTEAD OF triggers recursively, for exactly the reason you bring up.

Upvotes: 18

Related Questions