0

I have got table person which have got 2 triggers (if data is inserted, triggers make update) all triggers are after insert later I insert information about events to other table and I get (Update, Update, Insert) but should be this (Insert, Update, Update) do you know why?

I have got problem with this example:

    DECLARE @HistoryType CHAR(1) --"I"=insert, "U"=update, "D"=delete
    SET @HistoryType=NULL

    IF EXISTS (SELECT *
               FROM   inserted)
      BEGIN
          IF EXISTS (SELECT *
                     FROM   deleted)
            BEGIN
                --UPDATE
                SET @HistoryType='U'
            END
          ELSE
            BEGIN
                --INSERT
                SET @HistoryType='I'
            END
      END
    ELSE IF EXISTS(SELECT *
              FROM   deleted)
      BEGIN
          --DELETE
          SET @HistoryType='D'
      END
    IF @HistoryType='U' or @HistoryType='I'
   //do something
    END

Example from this post:

How to copy an inserted,updated,deleted row in a SQL Server trigger(s)

Community
  • 1
  • 1
Rafał Developer
  • 2,135
  • 9
  • 40
  • 72

1 Answers1

4

There is no guaranteed order to trigger firing, unless you use sp_settriggerorder. But then you'll have to set this every time you run ALTER TRIGGER.

Personally, I would never have 2 triggers on the same action, so I would do one of these

  • Use a single trigger to make things simpler
  • Use a stored procedure so the logic is more transparent

This includes things having both before and after triggers: this begs for a stored procedure.

gbn
  • 422,506
  • 82
  • 585
  • 676