Update-Insert Trigger how to fire after all rows are updated

  • pandionk1977

    SSC-Addicted

    Points: 445

    Hello,

    I'm pretty new to SQL and T-SQL, but after we created a database i have managed to make a trigger so that fields get updated from their relations with other tables.

    However now someone has written a program that allows people to update-insert data into the table, but the trigger fires after each insert or update and although not a huge slowdown yet, but taking 3 minutes for everything to be done seems like a loong time to me so wondering if it is possible to write the trigger to only fire after all the updates or inserts are done?

    Below is the trigger that i made, but dont have access to the C# program someone wrote sadly enough.

    USE [Traffic]

    GO

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE TRIGGER [dbo].[Blanco_B2_auto_update]

    ON [dbo].[Blanco_B2]

    AFTER INSERT,UPDATE

    AS

    BEGIN

    SET NOCOUNT ON;

    UPDATE dbo.Blanco_B2

    SET

    LO_ID=dbo.Locaties.LO_ID,

    Locatie=dbo.Locaties.Locatie

    from dbo.Locaties

    where dbo.Locaties.[Locatie_Afkorting]=dbo.Blanco_B2.[Locatie_Afkorting]

    UPDATE dbo.Blanco_B2

    SET

    BR_ID=dbo.Branches.BR_ID,

    Branche=dbo.Branches.Branche

    from dbo.Branches

    where dbo.Branches.[Branche_Afkorting]=dbo.Blanco_B2.[Branche_Afkorting]

    UPDATE dbo.Blanco_B2

    SET

    KPKD_ID=dbo.Kostenplaatsdrager.KPKD_ID,

    Kostenplaats=dbo.Kostenplaatsdrager.Kostenplaats

    from dbo.Kostenplaatsdrager

    where dbo.Kostenplaatsdrager.[Kostendrager]=dbo.Blanco_B2.[Kostendrager]

    END

  • Peter Lavelle-397610

    SSC Veteran

    Points: 215

    Hi pandionk1977,

    What you have is a recursive trigger.

    After an Insert or Update to table Blanco_B2 you are running another 3 Updates to table Blanco_B2 which will each fire the trigger again..........

    Try the following:

    1. Drop the trigger.

    2. Add the updates to columns LO_ID, Locatie, BR_ID, Branche, KPKD_ID, Kostenplaats back into whatever Inserts or Updates that cause the trigger to fire.

    3. Make sure you have indexes on the joined table columns:

    Locaties.[Locatie_Afkorting]

    Branches.[Branche_Afkorting]

    Kostenplaatsdrager.[Kostendrager]

  • pandionk1977

    SSC-Addicted

    Points: 445

    Hi,

    Thank u, i'm in the process now to adjust everything and see if i can make it work easier and faster that way then. NEver used indexes before, but then again I am a newbie so still learning 🙂

  • Johan Bijnens

    SSC Guru

    Points: 134297

    to lower the impact of your trigger, involve the trigger objects inserted/deleted.

    In your case, this may be one of your queries ...

    UPDATE B2

    SET LO_ID = L.LO_ID

    , Locatie = L.Locatie

    from dbo.Blanco_B2 B2

    inner join inserted I

    on I.keycol = B2.keycol

    inner join dbo.Locaties L

    on L.[Locatie_Afkorting] = I.[Locatie_Afkorting]

    Johan


    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt ?

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me[/url] :alien: but most of the time this is me :hehe:

  • Johan Bijnens

    SSC Guru

    Points: 134297

    -

    Johan


    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt ?

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me[/url] :alien: but most of the time this is me :hehe:

  • Orlando Colamatteo

    SSC Guru

    Points: 182269

    Peter Lavelle-397610 (4/25/2012)


    Hi pandionk1977,

    What you have is a recursive trigger.

    After an Insert or Update to table Blanco_B2 you are running another 3 Updates to table Blanco_B2 which will each fire the trigger again..........

    That is not how SQL Server AFTER triggers work by default. They only work that way if the RECURSIVE_TRIGGERS option is ON for the database, which is a rare need, and not likely in this case, but we should check with the OP.

    @pandionk1977 can you please tell us what this returns after changing the database name in the WHERE-clause to your database name?

    SELECT name,

    is_recursive_triggers_on

    FROM sys.databases

    WHERE name = N'AdventureWorks2008R2';

    When RECURSIVE_TRIGGERS is OFF (again, the default, for good reason), if you're in the scope of an AFTER trigger you can update the base table as many times as you want and the AFTER trigger will not fire again. Here is some code to demonstrate:

    USE AdventureWorks2008R2

    GO

    IF EXISTS ( SELECT *

    FROM sys.objects

    WHERE object_id = OBJECT_ID(N'dbo.TestTable')

    AND type IN (N'U') )

    DROP TABLE dbo.TestTable;

    GO

    IF EXISTS ( SELECT *

    FROM sys.objects

    WHERE object_id = OBJECT_ID(N'dbo.TriggerFired')

    AND type IN (N'U') )

    DROP TABLE dbo.TriggerFired;

    GO

    CREATE TABLE dbo.TestTable (Id INT, Name VARCHAR(100));

    GO

    CREATE TABLE dbo.TriggerFired

    (

    Id INT IDENTITY(1, 1),

    TriggerName SYSNAME,

    Fired DATETIME2 CONSTRAINT [df_dbo.TriggerFired.Fired] DEFAULT (GETDATE())

    );

    GO

    CREATE TRIGGER dbo.TestTable_after_iu ON dbo.TestTable

    AFTER INSERT, UPDATE

    AS

    BEGIN;

    SET NOCOUNT ON;

    INSERT INTO dbo.TriggerFired

    (TriggerName, Fired)

    VALUES (OBJECT_NAME(@@PROCID), DEFAULT);

    UPDATE dbo.TestTable

    SET Name = 'Rick'

    WHERE Id = 1;

    END;

    GO

    Setup is done...now lets try it:

    INSERT INTO dbo.TestTable

    (Id, Name)

    VALUES (1, 'Joe');

    SELECT *

    FROM dbo.TriggerFired;

    -- only returns one row, i.e. no recursive activity!

    UPDATE dbo.TestTable

    SET Name = 'Joe'

    WHERE Id = 1;

    SELECT *

    FROM dbo.TriggerFired;

    -- only returns 2 rows, not 3 or 4, again, no recursive activity!

    GO

    Now let's reset our tables and try it with RECURSIVE_TRIGGERS ON:

    -------------------------------------------------------------------

    TRUNCATE TABLE dbo.TriggerFired

    GO

    TRUNCATE TABLE dbo.TestTable

    GO

    ALTER DATABASE AdventureWorks2008R2 SET RECURSIVE_TRIGGERS ON;

    GO

    INSERT INTO dbo.TestTable

    (Id, Name)

    VALUES (1, 'Joe');

    -- woops, error! too many recursions...we would need

    -- to start looking into using @@NESTLEVEL in our trigger to get out of this mess!

    Cleanup code:

    -------------------------------------------------------------------

    --cleanup

    ALTER DATABASE AdventureWorks2008R2 SET RECURSIVE_TRIGGERS OFF;

    GO

    IF EXISTS ( SELECT *

    FROM sys.objects

    WHERE object_id = OBJECT_ID(N'dbo.TestTable')

    AND type IN (N'U') )

    DROP TABLE dbo.TestTable;

    GO

    IF EXISTS ( SELECT *

    FROM sys.objects

    WHERE object_id = OBJECT_ID(N'dbo.TriggerFired')

    AND type IN (N'U') )

    DROP TABLE dbo.TriggerFired;

    GO

    __________________________________________________________________________________________________
    There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

  • tfifield

    SSCrazy Eights

    Points: 9655

    Orlando,

    There is another situation where even if recursive triggers is turned off in the database, you can still get recursion from what I call cross-firing triggers. That is when a trigger in table A updates table B and a trigger in table B updates table A. I've actually seen cases where the business logic on this type of thing is valid - mostly with items sold vs the same items purchased. You have to be careful when doing this.

    Here's a simple scenario (not an actual one since it would be kind of silly to work both tables off of the same cost);

    CREATE TABLE Item

    ( ItemNo VARCHAR(10) NOT NULL

    , VendNo VARCHAR(10)

    , Descr VARCHAR(30)

    , LastCost DEC(15, 2)

    )

    GO

    ALTER TABLE Item ADD CONSTRAINT PK_Item

    PRIMARY KEY CLUSTERED (ItemNo)

    GO

    CREATE TABLE VendItem

    ( ItemNo VARCHAR(10) NOT NULL

    , VendNo VARCHAR(10) NOT NULL

    , Descr VARCHAR(30)

    , UnitCost DEC(15, 2)

    )

    GO

    ALTER TABLE VendItem ADD CONSTRAINT PK_VendItem

    PRIMARY KEY CLUSTERED (ItemNo, VendNo)

    GO

    INSERT INTO Item

    ( ItemNo, VendNo, Descr, LastCost, LastUpdated )

    VALUES ('ITEM', 'VENDOR', 'Sell Item', 1.95, GETDATE())

    GO

    INSERT INTO VendItem

    ( ItemNo, VendNo, Descr, UnitCost )

    VALUES ( 'ITEM', 'VENDOR', 'Purch Item', .85 )

    GO

    CREATE TABLE ItemTriggerTbl

    ( RecID INT IDENTITY(1, 1)

    , SomeText VARCHAR(30)

    )

    GO

    -- Trigger on Item table AFTER Update

    CREATE TRIGGER Item_ITrig ON Item AFTER UPDATE

    AS

    INSERT INTO ItemTriggerTbl

    ( SomeText )

    VALUES ('Begin Trigger')

    IF UPDATE(LastCost)

    UPDATE VI

    SET VI.UnitCost = I.LastCost

    FROM inserted I

    INNER JOIN VendItem VI ON

    I.ItemNo = VI.ItemNo

    AND I.VendNo = VI.VendNo

    GO

    CREATE TRIGGER VendorItem_ITrig ON VendItem AFTER UPDATE

    AS

    IF UPDATE(UnitCost)

    UPDATE Itm

    SET Itm.LastCost = I.UnitCost

    FROM inserted I

    INNER JOIN Item Itm ON

    I.ItemNo = Itm.ItemNo

    GO

    UPDATE Item

    SET LastCost = .80

    WHERE ItemNo = 'ITEM'

    /*

    Msg 217, Level 16, State 1, Procedure VendorItem_ITrig, Line 4

    Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

    */

    The triggers keep firing each other. So one of them has to test for being fired from another trigger:

    ALTER TRIGGER Item_ITrig ON Item AFTER UPDATE

    AS

    INSERT INTO ItemTriggerTbl

    ( SomeText )

    VALUES ('Begin Trigger')

    IF TRIGGER_NESTLEVEL() > 1

    RETURN

    IF UPDATE(LastCost)

    UPDATE VI

    SET VI.UnitCost = I.LastCost

    FROM inserted I

    INNER JOIN VendItem VI ON

    I.ItemNo = VI.ItemNo

    AND I.VendNo = VI.VendNo

    GO

    UPDATE Item

    SET LastCost = .82

    WHERE ItemNo = 'ITEM'

    SELECT *

    FROM ItemTriggerTbl

    You will see 2 records in ItemTriggerTbl. One for the original update statement and one for the update from the VendItem trigger. Since there was a test for TRIGGER_NESTLEVEL() in the Item trigger, the update was never done to the VendItem table and so the recursion ended there.

    Todd Fifield

  • GSquared

    SSC Guru

    Points: 260824

    On the original question about firing a trigger once for multiple updates: Yes.

    In SQL Server, a trigger only fires one time for any given Update/Insert/Delete statement, no matter how many rows it affects. If you update 100 rows or 1 row or 1,000 rows all at once, the trigger only fires one time.

    HOWEVER! That doesn't sound like the situation you are in. Most likely, the C# application is updating one row at a time instead of batching the updates into a single command. One update at a time is normal and usual, because it's generally a lot easier to code. So, if the front-end sends 10 update commands to SQL Server, those will be treated as separate transactions, separate commands, and the trigger will fire once for each one.

    If that's the case, that the front-end sends the updates one at a time, there is no way to get a trigger to fire once for all of them. You'd have to move that out of a trigger and into a separate command from the front-end. After all, SQL Server can't possibly know when the user is done updating things, so it can't have any way to know when to fire a trigger in that kind of situation.

    So, either move the code out of the trigger into a proc or script that the front-end runs based on the user clicking a button that says "I'm done" (or something like that), or you'll need to tune the trigger so that it runs fast enough that running multiple times no longer matters.

    If you can count on end-users always clicking "I'm Done" when they finish, which seems dubious to me (I've known too many end users), that might be fine. Otherwise, you'll need to speed up the trigger.

    Does that make sense?

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • GSquared

    SSC Guru

    Points: 260824

    On the subject of speeding up the trigger:

    Can you provide scripts to create the tables it affects (Blanco_B2, Branches, Kostenplaatsdrager, Locaties)? If so, I can test this. Also, please confirm that your server is SQL 2008, as per the forum. That affects whether this will work or not.

    Here's what I have so far that might help on performance:

    CREATE TRIGGER [dbo].[Blanco_B2_auto_update] ON [dbo].[Blanco_B2]

    AFTER INSERT, UPDATE

    AS

    BEGIN

    SET NOCOUNT ON ;

    MERGE INTO dbo.Blanco_B2 AS Tgt

    USING

    (SELECT INSERTED.ID, -- Whatever column name in Blanco_B2 is used as the primary key

    Locaties.LO_ID,

    Locaties.Locatie,

    Branches.BR_ID,

    Branches.Branche,

    Kostenplaatsdrager.KPKD_ID,

    Kostenplaatsdrager.Kostenplaats

    FROM INSERTED

    INNER JOIN dbo.Locaties

    ON dbo.Locaties.[Locatie_Afkorting] = inserted.[Locatie_Afkorting]

    INNER JOIN dbo.Branches

    ON dbo.Branches.[Branche_Afkorting] = inserted.[Branche_Afkorting]

    INNER JOIN dbo.Kostenplaatsdrager

    ON dbo.Kostenplaatsdrager.[Kostendrager] = inserted.[Kostendrager]) AS Src

    ON Tgt.ID = Src.ID -- Not sure about this. Use whatever column in Blanco_B2 is appropriate

    WHEN MATCHED

    THEN UPDATE

    SET LO_ID = Src.LO_ID,

    Locatie = Src.Locatie,

    BR_ID = Src.BR_ID,

    Branche = Src.Branche,

    KPKD_ID = Src.KPKD_ID,

    Kostenplaats = Src.Kostenplaats ;

    END ;

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Orlando Colamatteo

    SSC Guru

    Points: 182269

    ...looking again...

    __________________________________________________________________________________________________
    There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

  • Orlando Colamatteo

    SSC Guru

    Points: 182269

    I had to check again (hence my last post) since the trigger is for INSERT & UPDATE...wanted to retest...my response is unchanged:

    tfifield (5/3/2012)


    Orlando,

    There is another situation where even if recursive triggers is turned off in the database, you can still get recursion from what I call cross-firing triggers.

    No arguments there. That is not the case we are talking about here though...

    My comment, underline added:

    When RECURSIVE_TRIGGERS is OFF (again, the default, for good reason), if you're in the scope of an AFTER trigger you can update the base table as many times as you want and the AFTER trigger will not fire again.

    This is where Peter's assumption went awry...if you look at the OP's trigger definition it is only ever updating the base table, Blanco_B2.

    __________________________________________________________________________________________________
    There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply