Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2003
    Posts
    41

    Unanswered: Basic Trigger Firing Question

    If I insert 20 records into a table which has an INSERT trigger on it, does the trigger fire once for each record, or once at the end of all the inserts?

    I seem to be having a problem where the trigger is firing only for some of the inserted records but not others, seemingly randomly (although I know there must be some logical explanation for how it's choosing which record to fire under).

    Thanks =) Matt

  2. #2
    Join Date
    Feb 2003
    Posts
    41
    Here's how I ended up getting multirow inserts to work properly using an INSTEAD OF trigger rather than a AFTER trigger.

    -- INSTEAD OF Trigger That Fires Single-Row AFTER Triggers for a Table with IDENTITY

    CREATE TRIGGER trg_T1IOIS ON T1 INSTEAD OF INSERT
    AS

    SELECT IDENTITY(int, 1, 1) AS key_col, data_col
    INTO #t1
    FROM inserted

    DECLARE @key AS int
    SELECT @key = MIN(key_col) FROM #t1

    WHILE @key IS NOT NULL
    BEGIN

    INSERT INTO T1
    SELECT data_col FROM #t1 WHERE key_col = @key

    SELECT @key = MIN(key_col)
    FROM #t1
    WHERE key_col > @key

    END
    GO

  3. #3
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Each Trigger written should be able to deal with either a Single Insert/Delete/update or Bulk Insert/Delete/update

    I tend to use the following Format :-

    BEGIN
    IF @@RowCount =1
    BEGIN
    /*DO SINGLE ROW OPERATION*/
    END
    ELSE
    BEGIN
    /*DO MULTI ROW OPERATION*/
    END
    END

    Generally I alter the TSQL to cope with both eventualities
    and I believe it is good practice to apply this to ALL Triggers unless of course there are special cicumstances.

    Hope this Helps

    GW
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •