Trigger problem

Question

Trigger problem

henrik_fogelberg

Grasshopper

Points: 20
More actions
October 1, 2007 at 3:07 am

#181764

Hi,
I wrote the following trigger
CREATE TRIGGER trRestorder
ON eStore_CatalogProducts
FOR UPDATE
AS
UPDATE eStore_CatalogProducts
SET AvailableFrom = '2040-01-01'
WHERE CanBeOrdered = 'Nej'
AND StockValue <1;
GO
When I did an update with Query analyzer to test the trigger I got the error message
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
Any suggestions?
Thanks,
Henrik

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

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

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 1

You need to set the nested triggers option of server configuration settings to 0 to stop the engine from executing the trigger recursively using the following snippet...

USE master;
GO
EXEC sp_configure 'nested triggers', '0';
RECONFIGURE WITH OVERRIDE;

--Ramesh

henrik_fogelberg Grasshopper Points: 20 More actions · Answer 2

henrik_fogelberg

Grasshopper

Points: 20

October 1, 2007 at 5:00 am

#737654

Thank you. It solved my problem.

James Goodwin Hall of Fame Points: 3691 More actions · Answer 3

It solved your problem, but I don't think it does what you want it to do. Your trigger is affecting EVERY ROW of the table regardless of which rows were updated. I would think you would only want to update the updated rows.

JimFive

SwePeso SSC-Dedicated Points: 39752 More actions · Answer 4

You can also specify which columns to check for UPDATE on.

Then there will be no nested triggers at all!

CREATE TRIGGER trRestorder ON eStore_CatalogProducts

FOR UPDATE

AS

IF UPDATE(CanBeOrdered) OR UPDATE(StockValue)

UPDATEcp

SETcp.AvailableFrom = '20400101'

FROMeStore_CatalogProducts AS cp

INNER JOININSERTED AS i ON i.PkCol = cp.PkCol

WHEREi.CanBeOrdered = 'Nej'

AND i.StockValue < 1

GO

N 56°04'39.16"
E 12°55'05.25"

win SSCommitted Points: 1569 More actions · Answer 5

You can also test the trigger recursion with function trigger_nestlevel (see BOL) and stop recursion when the nest level exceeds 0. This method is specific to 1 trigger only and does not affect the recursion of other triggers.

Cheers,

Win

clyde.b Valued Member Points: 72 More actions · Answer 6

I create a delete trigger in Table1, if ever i will delete certain record it will also delete that record in Table2. And in Table2 also i created a delete trigger which also performs the same as delete trigger in Table1. If you delete a certain record it will also delete that record in Table1. But when i delete certain record it will create an error "Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).". can you help me on this?

noeld SSC Guru Points: 96590 More actions · Answer 7

In your TRIGGER at the begining

do something like this:

IF @@NESTED_LEVEL > 1

BEGIN

RETURN

END

* Noel

win SSCommitted Points: 1569 More actions · Answer 8

I cannot judge whether your circular relationship between tables 1 and 2 is a good thing. To stop the recursion I would use Noeld's construct, but with function trigger_nestlevel( object_ID('triggername')).

In the trigger for table 1 use:

if trigger_nestlevel( object_ID('table_2_trigger')) > 1

return

In the trigger for table 2 use:

if trigger_nestlevel( object_ID('table_1_trigger')) > 1

return

@@nestlevel also counts active procedures. If your delete in table 1 is called by a general procedure (not a trigger) the trigger will terminate before it can carry out a cascading delete on table 2.

Cheers,

Win