SQL Server Performance

Triggers to run one after other

Discussion in 'General Developer Questions' started by dineshasanka, Sep 21, 2006.

  1. dineshasanka Moderator

    I have table which insert trigger is fire when records is inserted.

    When there are multiple records inserted. I don't want to execute triggers parallel. instead, I need to execute triggers one after other

    ----------------------------------------
    Contributing Editor, Writer & Forums Moderator
    http://www.SQL-Server-Performance.Com

  2. FrankKalis Moderator

  3. dineshasanka Moderator

    no sir, that is to run several triggers for given order


    My Case is this

    I have a table called A, with ONE insert trigger

    I insert Record 1,2,3,4 to table A. I believe that insert trigger is running for four time simultaneously.
    But I need four triggers to be run first records 1 then record 2 etc.

    ----------------------------------------
    Contributing Editor, Writer & Forums Moderator
    http://www.SQL-Server-Performance.Com

  4. FrankKalis Moderator

    Then I guess you shouldn't do multiple row INSERTs. [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  5. dineshasanka Moderator

  6. dineshasanka Moderator

    Frank,
    Actually data is insert from replication. So I don't have a control there too

    ----------------------------------------
    Contributing Editor, Writer & Forums Moderator
    http://www.SQL-Server-Performance.Com

  7. FrankKalis Moderator

    Can't you move the logic from the trigger to Sp's?

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  8. sudiptakr New Member

    For bulk insert, triger can't run several time. It will run one time only.
    if you want to run trigger every time your records are inserted, then you have to insert them one by one.

    Regards
    Sudipta K. Ghosh
  9. Roji. P. Thomas New Member

    quote:Originally posted by dineshasanka

    no sir, that is to run several triggers for given order

    My Case is this

    I have a table called A, with ONE insert trigger

    I insert Record 1,2,3,4 to table A. I believe that insert trigger is running for four time simultaneously.
    But I need four triggers to be run first records 1 then record 2 etc.
    Dinesh, I am not sure whether I am missing something. But if you insert four records together, then the trigger will fire only once.

    Roji. P. Thomas
    Microsoft SQL Server MVP
    http://toponewithties.blogspot.com
  10. Twan New Member

    Just to make it clear... by insert four records together we mean something like

    insert into ...
    select ...

    where the select returns 4 records

    or bcp or bulk insert

    four insert statements in the same transaction or batch will fire the trigger 4 times in the same sequence as the inserts
    begin tran
    insert into ... values ( 1, ...)
    insert into ... values ( 2, ...)
    insert into ... values ( 3, ...)
    insert into ... values ( 4, ...)
    commit tran

    will result in the trigger being fired four times

    Cheers
    Twan

  11. Adriaan New Member

    Perhaps the trigger doesn't handle batch inserts too well? The inserted conceptual table contains as many rows as were inserted, so you have to use set-based logic.

    But for instance the Jet database engine will do inserts, updates and deletes against ODBC-linked tables on SQL Server one row at a time, so in that case you will see the trigger fire for each row.
  12. dineshasanka Moderator

    but insert is happening from replication. it does execute trigger four times (number of recrodrs) . what I want is to execute triggers one after the other

    ----------------------------------------
    Contributing Editor, Writer & Forums Moderator
    http://www.SQL-Server-Performance.Com

  13. Adriaan New Member

    Only one trigger can fire for a given event on the same table. Until the trigger finishes, every other event on the table is suspended. So they are executing one after the other.
  14. dineshasanka Moderator

    quote:Originally posted by Adriaan

    Only one trigger can fire for a given event on the same table. Until the trigger finishes, every other event on the table is suspended. So they are executing one after the other.

    I can't beleive this.
    I have four records. depending on the record type record 1 will take some time to execute than the record 2. ( I am sending MSMQ from trigger)

    I need Record 1 trigger to be completed before record 2 start its.
    but I found that record1 MSMQ has not gone when record2 MSMQ has reached.
    That is why I think triggers are not executing one after the other.

    ----------------------------------------
    Contributing Editor, Writer & Forums Moderator
    http://www.SQL-Server-Performance.Com

  15. FrankKalis Moderator

    Then take a look at what I mentioned in my first reply. You can specify first and last trigger. In between order of execution is undefined and might or might not match your needs. May I mention that your whole logic here sounds a bit "strange"?

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  16. FrankKalis Moderator

    Oops, postings obviously have overlapped. When I opened this thread, your answer wasn't there.

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  17. dineshasanka Moderator

    I was about to reply.
    I have only one trigger so how can I set priorities.
    anyway, we have change the design!!!
    Actually this was working fine for our release 1. but due to other chages in current release this gave us some issues. mainly becuse record 1 trigger executes bit more duration now due to requiermetn change

    anyway good experience.

    ----------------------------------------
    Contributing Editor, Writer & Forums Moderator
    http://www.SQL-Server-Performance.Com

  18. FrankKalis Moderator

    Case closed? [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  19. dineshasanka Moderator

  20. Twan New Member

    Hi ya,

    I guess that multiple inserts could happen at the same time if the locking mechanisms allowed it...

    if you have to be absolutely sure that your MSMQ only fires one record at a time, then you can use sp_getapplock to control the locking with a semaphore...? But you'd have to handle the wait/rety and ensure that locks are released properly

    Cheers
    Twan
  21. Adriaan New Member

    Can you post the trigger code?

    Do you WANT the trigger to fire once for each row inserted? In that case, perhaps try looping through a cursor based on the inserted snapshot.

    Did I really suggest that? Uh, yes - and then I usually follow up with a request to post the code, to see if it can't be done with set-based logic. Nine times out of ten, it can be done set-based.
  22. Twan New Member

    By the sounds of it, the rows are inserted one at a time, and therefore the trigger will fire 4 times, but for whatever reason the triggers need to be executed in the same sequence as the inserts, with no overlap/parallel processing.

    So it should go
    insert record 1
    trigger for record 1
    insert record 2
    trigger for record 2
    insert record 3
    trigger for record 3
    insert record 4
    trigger for record 4

    rather than the triggers and inserts overlapping.

    This is not necessarily from a single application/user but across the database


    That is my understanding of the problem, is that right?

    Cheers
    Twan
  23. dineshasanka Moderator

  24. Adriaan New Member

    Add an explicit transaction to your trigger, and include the table itself in your query.

Share This Page