October 10, 2008 at 12:46 pm
hi guys a question, i have a after insert trigger that does an update in another table, this trigger will do updates thousands of times a day.
Is it a good practice to do transaction for this updates? TRY and CATCH ?what about to do a RAISEERROR event?
October 10, 2008 at 1:40 pm
I don't think it would make sense to do a transaction within the trigger, especially if it's just doing 1 UPDATE statement. What makes more sense is a transaction arround whatever INSERT statements put data into this table, or to implement this through a stored procedure that you always call to do the add a record operation instead of an INSERT statement directly. then you could put the transaction in the stored procedure arround the INSERT into table 1 and the UPDATE into table 2.
Using TRY...CATCH could be beneficial in at least a few situations:
- There are known conditions that cause errors that the trigger / stored procedure can recover from.
- There are other things happening in the trigger / stored procedure that you want to ensure get cleaned up (transactions are a good example of when I always do a TRY...CATCH)
- You want to log errors that occur in the trigger / stored procedure
If you are asking about RAISERROR in terms of checking some business rules or something in the trigger / stored procedure I suppose that's a personal preference that the database developers and application developers will just need to agree on how those situations will be handled.
October 11, 2008 at 8:40 am
A trigger always runs within the context of the transaction started by the statement that fired it. If you issue a rollback statement within a trigger you will roll back everything that the trigger did and whatever the operation that fired the trigger did.
As for try/catch, it depends. Does everything that is done in the trigger have to happen? If one piece fails do you want to undo the initial insert that fired the trigger?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 11, 2008 at 10:04 pm
GilaMonster (10/11/2008)
A trigger always runs within the context of the transaction started by the statement that fired it. If you issue a rollback statement within a trigger you will roll back everything that the trigger did and whatever the operation that fired the trigger did.As for try/catch, it depends. Does everything that is done in the trigger have to happen? If one piece fails do you want to undo the initial insert that fired the trigger?
Heh... DAMN! Beat me to it, again! 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
October 13, 2008 at 7:40 am
thank you for your replies, here is the code for my trigger:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[tr_updDistName] ON [dbo].[tblDistributors]
AFTER INSERT
AS
DECLARE @DistID_FK int,@CodeID_FK int,
@DateInserted datetime
Select @DistID_FK = (Select DistID_FK from Inserted),
@CodeID_FK = (Select CodeID_FK from Inserted),
@DateInserted= (Select DateInserted from Inserted)
IF (@CodeID_FK = 118)
BEGIN
UPDATE dbo.tblDistributor_Details SET dateMoved = @DateInserted
where DistID_FK = @DistID_FK
END
IF (@CodeID_FK = 119)
BEGIN
UPDATE dbo.tblDistributor_Details SET dateTransported = @DateInserted
WHERE DistID_FK = @DistID_FK
END
I have a couple of questions:
Is there a way to put that if a record is inserted into the table tblDistributors and this trigger gives an error for some kind then the new records is still inserted into the tblDistributors and maybe give an error for the trigger?
I am also having an issue when an execute a stored procedure that inserts multiple records in the table tblDistributors at the same time.
I get this error: "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !, =, or when the subquery is used as an expression." I believe is because in my trigger i have to select from insert?
Would it be wise to put a begin/catch in this trigger?
Can you help me please?
October 13, 2008 at 8:07 am
DBA (10/13/2008)
Select @DistID_FK = (Select DistID_FK from Inserted),
@CodeID_FK = (Select CodeID_FK from Inserted),
@DateInserted= (Select DateInserted from Inserted)
What's going to happen here is the insert affects more than one row?
Triggers in SQL fire once per operation, not once per row. There could be many rows in the inserted table. That's why you're getting a subquery error.
Try something like this
ALTER TRIGGER [dbo].[tr_updDistName] ON [dbo].[tblDistributors]
AFTER INSERT
AS
UPDATE dbo.tblDistributor_Details SET dateMoved = i.DateInserted
from tblDistributor_Details dd inner join inserted i on dd.DistID_FK = i.DistID_FK
WHERE i.CodeID_FK = 118
UPDATE dbo.tblDistributor_Details SET dateTransported = i.DateInserted
from tblDistributor_Details dd inner join inserted i on dd.DistID_FK = i.DistID_FK
WHERE i.CodeID_FK = 119
GO
As for whether to use try/catch or not, it depends. What do you want to happen if one of those updates throws an error?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 13, 2008 at 9:20 am
thanks! this is what i did and it seems to be working perfectly (still testing)
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[tr_updDistName] ON [dbo].[tblDistributors]
AFTER INSERT
AS
DECLARE @DistID_FK int,@DistID_FK int,
@DateInserted datetime
Select @DistID_FK = I.DistID_FK ,
@DistID_FK = I.DistID_FK ,
@DateInserted= I.DateInserted from
Inserted as I Left join dbo.tblDistributor_Details as D on --New
D.DistID_FK = I.DistID_FK --New
IF (@CodeID_FK = 118)
BEGIN
UPDATE dbo.tblDistributor_Details SET dateMoved = @DateInserted
where DistID_FK = @DistID_FK
END
IF (@CodeID_FK = 119)
BEGIN
UPDATE dbo.tblDistributor_Details SET dateTransported = @DateInserted
WHERE DistID_FK = @DistID_FK
END
what do you think?
in regards for your question, if the trigger fails to do the update i just want to be able to get an error message but still do the insert in the tblDistributors. is this possible?
Thanks for all your help!
October 13, 2008 at 9:41 am
That still only allows for a single row in inserted.
If multiple rows have been inserted, then your variable assignment (below) is going to assign values of one of those rows only and completely ignore all of the others
Select @DistID_FK = I.DistID_FK ,
@DistID_FK = I.DistID_FK ,
@DateInserted= I.DateInserted from
Inserted as I Left join dbo.tblDistributor_Details as D on --New
D.DistID_FK = I.DistID_FK --New
The code I gave you will handle any number of rows in inserted.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 13, 2008 at 10:11 am
A little advice: Can you move the logic on the trigger into an sp or a multi-statement transaction handled by the client-side ?
* Noel
October 13, 2008 at 12:08 pm
DBA (10/13/2008)
thank you, so i put my logic now how you put it. I could put the logic into a stored procedure, and then make the trigger execute the sp, would that be better?
No. Far worse as that will require a cursor in your trigger. Very slow.
What Noeld is suggesting is putting the updates and the insert in a stored proc and calling that proc from whereever this update is been called from. Then you don't need a trigger at all.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 13, 2008 at 1:55 pm
Thank you, i will follow your advice and leave the trigger using the inner joins, its working great. I solved my other problem of the rowcounts with the set nocount on :). Thank you so much for all your help!!!
October 23, 2008 at 9:17 am
hi, i have noticed that my trigger has been causing a lot of exclusive locks
here is my code
ALTER TRIGGER [dbo].[tr_updDistName] ON [dbo].[tblDistributors]
AFTER INSERT
AS
BEGIN
set nocount on;
UPDATE dbo.tblDistributor_Details SET dateMoved = i.DateInserted
from tblDistributor_Details dd inner join inserted i on dd.DistID_FK = i.DistID_FK
WHERE i.CodeID_FK = 118
UPDATE dbo.tblDistributor_Details SET dateTransported = i.DateInserted
from tblDistributor_Details dd inner join inserted i on dd.DistID_FK = i.DistID_FK
WHERE i.CodeID_FK = 119
END
set nocount off;
[/CODE]
could it be because i added the BEGIN, END? are these necessary?
October 23, 2008 at 10:06 am
The BEGIN and END are not necessary and they are not causing the locks. Exclusive locks are necessary in order to do the updates, you don't want someone else to be making a change at the same time as you, nor do you want someone reading inaccurate data.
You may be getting locks you don't need because of poor indexes or because of out of date statistics that cause the transaction to take bigger locks (page/index/table) locks than should be necessary.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 23, 2008 at 10:15 am
The BEGIN and END may not be necessary, but I find it makes it easier to see what code makes up a trigger, stored procedure etc. Especially true if there are several together in a single script.
😎
October 23, 2008 at 10:22 am
An UPDATE is going to cause an exclusive lock no matter what. Of course - it should be only for the length of time it takes to actually update the row.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply