ERROR TRIGGER - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

ERROR TRIGGER

Author

Topic

Anand.A
Posting Yak Master

109 Posts

Posted - 2012-01-20 : 01:07:04

HI
in ssms i wrote after update trigger like this
in my ssis package by using execute sql task i will update that error_rows_count
so i wrote after update trigger in ssms for that table
like this:
CREATE TRIGGER LOAD_STAT
ON LOAD_STAT
AFTER UPDATE
AS
DECLARE @ERROR_ROWS NUMERIC(20);
SELECT @ERROR_ROWS =ERROR_ROWS_COUNT
FROM LOAD_STAT
if
@ERROR_ROWS >=1
begin
UPDATE LOAD_STAT SET EXEC_STATUS_FLAG ='W';
end
else
UPDATE LOAD_STAT SET EXEC_STATUS_FLAG ='S';

if i try to insert rows its show error
how to solve this
Msg 217, Level 16, State 1, Procedure LOAD_STAT Line 17
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

anand

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-20 : 06:39:08

You are updating the table which caused the trigger to fire from within the trigger. That means, the trigger will get fired again recursively, infinitely. SQL server has a limit of 32 such recursive calls. So you have a logical error in your trigger.

Also, your trigger has the same name as the table itself. I am surprised that you are able to do that. I thought the object names had to be unique.

Can you update EXEC_STATUS_FLAG immediately after the update statement, rather than using a trigger to do it?

Kristen
Test

22859 Posts

Posted - 2012-01-20 : 09:23:14

"You are updating the table which caused the trigger to fire from within the trigger"

We do that all the time without any problems. I'm not aware that we have turned OFF any recursive-trigger-calls option, so if this is the problem for the user maybe they have adjusted a setting somewhere?

Also, O/P says "if i try to insert rows" which doesn't sound right with an AFTER UPDATE trigger?

Also x2 I would be bothered that the

UPDATE LOAD_STAT SET EXEC_STATUS_FLAG ...

statements are not constrained to the rows being effected - i.e. the whole table is being updated, rather than just the row(s) being updated [and firing the trigger] - that might just be lack of understanding on the O/P's part?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-20 : 10:04:10

quote:
We do that all the time without any problems. I'm not aware that we have turned OFF any recursive-trigger-calls option, so if this is the problem for the user maybe they have adjusted a setting somewhere?

Kristen, You are right, recursive triggers are turned off by default. If recursive triggers option is turned off the behavior OP is seeing should not happen (assuming of course, the message is coming from the trigger).

SELECT is_recursive_triggers_on FROM sys.databases WHERE NAME = 'thedatabasename'

The above select statement should show what the setting is. If it indeed is turned on (which I think it is), someone must have turned it on (either for the database or in the model), in which case I would be worried about turning it OFF.

quote:
Also, O/P says "if i try to insert rows" which doesn't sound right with an AFTER UPDATE trigger?

I suspect he was trying to insert the rows and then do the update to the error_rows_count, all from his SSIS package, as he mentioned in his post "in my ssis package by using execute sql task i will update that error_rows_count"

Kristen
Test

22859 Posts

Posted - 2012-01-20 : 10:13:19

"in which case I would be worried about turning it OFF."

Me too!

"all from his SSIS package,"

Ah, that explains it. I'm not familiar with SSIS "workings"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources