CODEWITHSUNDEEP
c#sql.netsql-servertriggers
maryam mohammadi
maryam mohammadi

Reputation: 694

How to have nested trigger in sql?

i have a After insert/update trigger. It should gets a field value (lets call it "CurrentState") from inserted record. check CurrentState value in another table, then find another value for this field and then update the newly added or updated record with this value. so after a record update, the currentstate might be updated several times. Does SQL support nested update trigger?! actually it doesn't work now, i didn't found any mistake in my code. so i get suspicious about sql support.

here is my code

create  TRIGGER [dbo].[ASI_trigBPMIAutomaticState]
   ON  [dbo].[ASI_TblBPMForm]
   AFTER INSERT, Update
AS 
BEGIN

    select @CurrentState  = CurrentState from inserted;
    select @FormID = FormID from inserted;

    DECLARE curflow CURSOR 
    LOCAL SCROLL STATIC
    for select w.WorkflowID
    from ASI_TblBPMWorkflow w
    where  w.FromState = @CurrentState

    OPEN curflow

    FETCH first FROM curflow
    INTO @WorkflowID            
    WHILE @@FETCH_STATUS = 0
    BEGIN       
        select @ToState = StateID
        from TblStates
        where r.ResponseID = @WorkflowID

        if (@ToState  =1)
            breake;

        FETCH next FROM curflow
        INTO @WorkflowID
    END -- While Cursor

    CLOSE curflow
    DEALLOCATE curflow  

    if (@QID > 0)   
    begin   
        update ASI_TblBPMForm
        set CurrentState = @ToState
        where FormID = @FormID
    end;
end

Upvotes: 0

Views: 3361

Answers (1)

Mitch Wheat
Mitch Wheat

Reputation: 300719

Does SQL support nested update trigger?!

Yes.

Create Nested Triggers

Both DML and DDL triggers are nested when a trigger performs an action that initiates another trigger. These actions can initiate other triggers, and so on. DML and DDL triggers can be nested up to 32 levels. You can control whether AFTER triggers can be nested through the nested triggers server configuration option. INSTEAD OF triggers (only DML triggers can be INSTEAD OF triggers) can be nested regardless of this setting.

There are nested triggers and recursive triggers.

ALTER DATABASE AdventureWorks2012
   SET RECURSIVE_TRIGGERS ON;
GO

[Please take note of @marc_s's comments.] I would strongly suggest you do not use cursors in triggers, and make sure your trigger can handle multiple rows.

Upvotes: 1

Related Questions