Nested Trigger and Nested Trigger Limit

Lakhwinder.Ghuman
Posted by Lakhwinder.Ghuman under Sql Server category on | Points: 40 | Views : 3507
Trigger:
A set of statements that executes automatically whenever any operation on a specified table is performed to modify data.
Nested Trigger:
A trigger can be nested or recursive in nature. When a statement in a trigger fires same or another trigger then a trigger is called a Nested/ Recursive trigger. A very simple example of a nested trigger is as:
Let's we have a simple table 'TableXYZ ' with following structure:
Id int
Description varchar(50)
And we have a simple 'after insert' trigger as:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE TRIGGER trgNested
ON TableXYZ
AFTER INSERT
AS
BEGIN

SET NOCOUNT ON;

INSERT INTO TableXYZ(Description) SELECT Description FROM INSERTED

END
GO


Here the trigger fires after insert operation and a statement within trigger itself performs INSERT operation which leads it to infinite loop. SQL server supports nested trigger up to 32 levels so this trigger will fire with error message
'Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32) .'

We can set the nesting of triggers on database level with the help of following statement:
ALTER DATABASE  nameofdatabase  SET RECURSIVE_TRIGGERS OFF


We can also restrict nesting/ recursion of trigger up to certain level with the help of following statement:
	IF ((SELECT TRIGGER_NESTLEVEL()) > 10 )
RETURN

Comments or Responses

Posted by: Muhsinathk on: 9/13/2012 Level:Bronze | Status: [Member] | Points: 10
A trigger is a pl/sql block structure which is fired when a DML statements like Insert, Delete, Update is executed on a database table. A trigger is triggered automatically when an associated DML statement is executed.

Syntax of Triggers

The Syntax for creating a trigger is:

CREATE [OR REPLACE ] TRIGGER trigger_name

{BEFORE | AFTER | INSTEAD OF }

{INSERT [OR] | UPDATE [OR] | DELETE}

[OF col_name]

ON table_name

[REFERENCING OLD AS o NEW AS n]

[FOR EACH ROW]

WHEN (condition)

BEGIN

--- sql statements

END;
SQL Server 2000 has many types of triggers:

1. After Trigger
2. Multiple After Triggers
3.Instead Of Triggers
4. Mixing Triggers Type
Posted by: Muhsinathk on: 9/13/2012 Level:Bronze | Status: [Member] | Points: 10
Differences between storedprocedures and triggers
1. When you create a trigger you have to identify event and action of your trigger but when you create s.p you don't identify event and action

2.Trigger is run automatically if the event is occured but s.p don't run automatically but you have to run it manually

3. Within a trigger you can call specific s.p but within a sp you cannot call atrigger

4.Trigger execute implicitly whereas store procedure execute via procedure call from another block.

5.We can call a stored procedure from front end (.asp files, .aspx files, .ascx files etc.) but we can't call a trigger from these files.

6. Stored procedure can take the input parameters, but we can't pass the parameters as an input to a trigger.

Login to post response