April 25, 2012 at 11:00 pm
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
April 25, 2012 at 11:32 pm
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]
April 25, 2012 at 11:51 pm
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 🙂
April 26, 2012 at 12:21 am
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
Learn to play, play to learn !
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 but most of the time this is me
April 26, 2012 at 12:24 am
-
Johan
Learn to play, play to learn !
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 but most of the time this is me
April 26, 2012 at 10:23 am
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
May 3, 2012 at 12:18 pm
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
( ItemNoVARCHAR(10) NOT NULL
, VendNoVARCHAR(10)
, DescrVARCHAR(30)
, LastCostDEC(15, 2)
)
GO
ALTER TABLE Item ADD CONSTRAINT PK_Item
PRIMARY KEY CLUSTERED (ItemNo)
GO
CREATE TABLE VendItem
( ItemNoVARCHAR(10)NOT NULL
, VendNoVARCHAR(10) NOT NULL
, DescrVARCHAR(30)
, UnitCostDEC(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
( RecIDINT IDENTITY(1, 1)
, SomeTextVARCHAR(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
May 3, 2012 at 12:35 pm
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
May 3, 2012 at 12:50 pm
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
May 3, 2012 at 1:20 pm
...looking again...
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 3, 2012 at 1:30 pm
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 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply