October 14, 2008 at 3:31 pm
Nice article. So what's considered excessive trigger use? We have some triggers that are 2000 lines and do a whole lot of data cleanup. They fire recursively too and we've occasionally hit that 32 level limit.
October 14, 2008 at 3:59 pm
I don't know that I'd call a trigger excessive because of the number of lines of code, but more by what it does and how long it takes. I would consider a trigger that recurses to be a candidate for evaluation. It sounds like the processing you are doing needs to be moved out of the trigger though.
If the triggers are part of an ETL process I'd look into SSIS or another way to process the data.
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 14, 2008 at 4:41 pm
Jack Corbett (10/14/2008)
Robert,
Thanks for the comment. I believe you are incorrect in stating that the transaction will not fail on an error with the trigger. An error in a trigger will cause a rollback in my experience. Your are correct in stating that the client application should handle it, and then you can re-submit a corrected transaction, but left to itself the data modification is rolled back either explicitly like in your code, or implicitly.
You're right with your test case, but that's the side effect of try..catch. The client gets the result of a select in catch block, but no error.
Try this:
create table dbo.testXa
( a integer)
go
create trigger [dbo].[ti_test] on [dbo].[testXa] after insert
as raiserror 19999 'Sorry, not insertable'
go
select * from testxa
--emtpy table
insert into testxa values (123)
--we got an error
select * from testxa
--insert took place anyway
begin try
insert into testxa values (124)
end try
begin catch
SELECT 'Error in Trigger' AS error, Error_Message() AS MESSAGE
end catch
go
select * from testxa
--no insert took place
drop table dbo.testXa
The first insert is not rolled back. You usually use try..catch in triggers and stored procs when necessary, but client application generated update statements are usually plain update statements without try...catch.
I asked some time ago how to prevent such updates. One of options is INSTEAD OF trigger, which is not as handy as BEFORE trigger on other servers, but serves the purpose.
October 14, 2008 at 5:30 pm
Robert,
Using RaisError the way you use it in your example is generating an error message for the client, but is not actually an error in the trigger which is why the transaction is completing. As far as SQL Server is concerned the error has been handled and the trigger returns 0 for success. To demonstrate try this code:
[font="Courier New"]CREATE PROCEDURE dbo.test
AS
--select 'test'
RAISERROR 19999 'test';
GO
DECLARE @retval INT
EXEC @retval = dbo.test
SELECT @retval
DROP PROCEDURE dbo.test[/font]
For a trigger with an actual error the transaction is rolled back. In the following code I am trying to insert a value too large for the data type so the outer transaction fails in both cases:
[font="Courier New"]CREATE TABLE dbo.testXa
(
a integer
)
CREATE TABLE dbo.TestXb
(
b tinyint
)
GO
CREATE TRIGGER [dbo].[ti_test] ON [dbo].[testXa]
after INSERT
AS
INSERT INTO dbo.TestXb
SELECT
*
FROM
inserted
GO
SELECT
*
FROM
testxa
--emtpy table
INSERT INTO testxa
VALUES
(
1234
)
--we got an error, not raised an error so no insert
SELECT
*
FROM
testxa
SELECT
*
FROM
testxb
GO
BEGIN try
INSERT INTO testxa
VALUES
(
12345
)
END try
BEGIN catch
SELECT
'Error in Trigger' AS error,
Error_Message() AS MESSAGE
END catch
GO
--no insert took place
SELECT
*
FROM
testxa
DROP TABLE dbo.testXa
DROP TABLE dbo.testXb
[/font]
Even if I use Try-Catch in the trigger I get a rollback:
[font="Courier New"]CREATE TRIGGER [dbo].[ti_test] ON [dbo].[testXa]
after INSERT
AS
BEGIN try
--will not work due to overflow
INSERT INTO dbo.TestXB
SELECT
*
FROM
inserted
END Try
BEGIN Catch
DECLARE @error VARCHAR(500)
SET @error = Error_Message()
SELECT @error
END Catch[/font]
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 14, 2008 at 5:44 pm
Robert,
You can stop an update from occurring by issuing a rollback within the trigger.
October 15, 2008 at 1:47 am
Jack, to my dismay, you're absolutely right. Raiserror in this form is just a message and execution continues normally.
I uncommented Select 'test' and added return 1 after raiserror and both are returned with error message.
Well, never too late to learn new things.
October 15, 2008 at 6:43 am
Robert,
I'm gald we got this sorted out. I didn't go in trying to prove anything only to understand how it works.
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
November 3, 2008 at 6:53 pm
Dang it, Jack! Somehow I missed this article when it was published. Found it as a link in one of your good posts. Awesome article especially for folks new to T-SQL.
Any chance of talking you into writing one about "Instead Of" triggers? 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
November 4, 2008 at 5:40 pm
Thanks Jeff. I could write something more detailed about INSTEAD OF triggers, but I've never actually used one in one of my databases. I use stored procedures to do anything I'd do in an INSTEAD OF trigger. That's why the INSTEAD OF section was so short.:)
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
November 11, 2008 at 4:41 am
hi Jack,
thanks for your articel.
but I still have a question (hopefully it is ok to post questions/problems here, if not: sorry folks, i'm quite new here):
I get a csv string from the UI that has to be devides in several columns.
this procedure has to run very fast, so i splitted the necessary actions (because otherwise it is to slow and some information is lost):
1. procedure which inserts the whole csv string in one column. (this proc has to check if one csv string contains only one or more rows and then inserts it into the table.)
like '1211224,45345,35636,3453453,3453456,...)
2. job, which copies the new data as it is to another sql-server every 5 minutes (necessary because UI is located on a DMZ and the information has to be stored in the internal sql server)
now the trigger problem:
if the new data is inserted, i run a trigger, which splits the information in the different columns. I do not see any possibility to make an update for all the rows in the inserted as you mentioned, because the split works with a function which returns the value before the next coma of the csv-string.
create trigger trtest
...
declare @Hoehe int, @kmStand int, @text varchar(200)
select @text = text from inserted
-- @text is the csv string, @hoehe, @kmstand .. are variables which are used in the update statement
set @text = dbo.fKomma(@text)
set @Hoehe = dbo.fWert(@text)
set @text = dbo.fKomma(@text)
set @kmStand = dbo.fWert(@text)
....
update table set
hoehe = @hoehe,
kmstand = @kmstand,....
where id = @id
go
as the trigger is fired only once, i implemented a procedure
which contains a cursor to make the insert and therefore the trigger is fired for each row.
create proc pTransfer
as
set nocount on
declare tcursor cursor for
select daten
from tblOrtungExt
order by idortung
declare @daten varchar(500)
open tcursor
fetch next from tcursor
into @daten
while @@FETCH_STATUS = 0
begin
-- insert in tabel with trigger
exec pInsertOrtung @daten
fetch next from tcursor
into @daten
end
close tcursor
deallocate tcursor
go
very long problem...sorry for that. so if you have any time left :), i would be glad about any suggestions.
Susanne
November 11, 2008 at 5:07 am
Jack Corbett (11/4/2008)
Thanks Jeff. I could write something more detailed about INSTEAD OF triggers, but I've never actually used one in one of my databases. I use stored procedures to do anything I'd do in an INSTEAD OF trigger. That's why the INSTEAD OF section was so short.:)
I think that everyone has the same thoughts there. I don't know anyone who has actually written an INSTEAD OF trigger for anything practical and wouldn't mind seeing one that's actually been tested and, hopefully, documented.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 13, 2008 at 7:46 am
The one thing I can think of off hand, (and I have never done this in practice) where you would need an instead of trigger is for being able to do updates against views where the view would not normally be updatable.
But except for some truly unusual situations that I have yet to come across in practice, you would be better off either forcing everyone to use a stored procedure to update that information or designing your tables so that you have no need to update the view.
Still, it is an option.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
November 13, 2008 at 8:23 am
Yeah, INSTEAD OF triggers are an option, but so aren't cursors. I know cursors do have their use, so I am sure INSTEAD OF triggers do as well. I just have not found one yet.
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
November 13, 2008 at 11:28 am
For ideas see Normalizing-Denormalized Tables http://www.sqlservercentral.com/articles/Normalization/64428/ and the discussion.
My suggestion:
You can expand on this. What you got is backward compatibility for select queries. Additionally, you can create "instead of" triggers on views, so you get update compatibility too. Idea for part II of the article.
🙂
November 13, 2008 at 12:51 pm
Heh. Robert and Mohit have a point. Instead of triggers views could be used to let a legacy application interact with and change the data in the way it is designed to, while storing it in a completely separate fashion.
Still, those circumstances should be few and far between indeed. It is much better to change the application as well, and it is better still (in most cases) to put all updates and inserts through stored procedures and then the stored procedures can be changed to fit the data layer and the application will never be the wiser.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply