Presents your 
SQL SERVER E-NEWSLETTER for December 10, 2002

<------------------------------------------->

RETURN THE VALUE OF NESTED TRIGGER LEVELS

The TRIGGER_NESTLEVEL() function, as well as the @@NESTLEVEL global
variable, will return the nested trigger level, an attribute that can be used
several ways. When a transaction initiates a trigger, which in turns
initiates an implicit transaction that will initiate another trigger, 
you have just created two trigger levels within the transaction.

There have been published designs for database lookup tables that have
one main table with which the application or front-end interfaces. This
allows for one object instantiation for many lookup tables instead of
having multiple objects instantiated for all the lookup tables. The one main
table has triggers that will maintain the data for all the lookup tables.

In such a database design, the TRIGGER_NESTLEVEL() function or
@@NESTLEVEL global variable can prevent users from entering data directly 
into the lookup table and bypassing the main table that maintains all the 
lookup tables.

Another use of the function or global variable is for breaking a
recursive trigger. There may be a situation where a trigger will initiate DML
that will initiate the current trigger again. You can check the nested
trigger level to break out of this recursive trigger situation.

Following is a sample script to demonstrate a child table not allowing
direct inserts into the table:

SET NOCOUNT ON
GO
--*************************************
--****  CREATE PARENT TABLE
--*************************************
CREATE TABLE NestedTrigger1
(pkey1 INT IDENTITY NOT NULL
     CONSTRAINT pk_NT1 PRIMARY KEY,
col1 INT NULL)
GO
--*************************************
--****  CREATE PARENT TABLE TRIGGER
--*************************************
CREATE TRIGGER ti_NestedTrigger1
ON NestedTrigger1
FOR INSERT
AS
BEGIN
     INSERT NestedTrigger2 (NT1pkey1, col1)
     SELECT pkey1, col1 FROM INSERTED
END
GO
--*************************************
--****  CREATE CHILD TABLE
--*************************************
CREATE TABLE NestedTrigger2
(pkey1 INT IDENTITY NOT NULL,
NT1pkey1 INT NOT NULL
     CONSTRAINT fk_NT2_NT1 FOREIGN KEY (NT1pkey1)
          REFERENCES NestedTrigger1 (pkey1),
col1 INT NULL)
GO
--*************************************
--****  CREATE CHILD TABLE TRIGGER
--*************************************
CREATE TRIGGER ti_NestedTrigger2
ON NestedTrigger2
FOR INSERT
AS
BEGIN
   IF ( SELECT TRIGGER_NESTLEVEL ( ) ) < 2
   BEGIN
     DECLARE @OwnerName sysname
     SELECT @OwnerName = OBJECT_NAME( @@PROCID  )
     RAISERROR ('Trigger, %s , will not allow insert.',10,1,@OwnerName)
     ROLLBACK
     RETURN
   END
END
GO
--*************************************
--****  INSERT INTO PARENT TABLE
--*************************************
INSERT NestedTrigger1 (col1) VALUES ( 1 )
GO
SELECT * FROM NestedTrigger1
GO
SELECT * FROM NestedTrigger2
GO
--*************************************
--****  INSERT INTO CHILD TABLE
--*************************************
INSERT NestedTrigger2 (NT1pkey1, col1) VALUES ( 1, 77 )
GO
SELECT * FROM NestedTrigger1
GO
SELECT * FROM NestedTrigger2
GO
--*************************************
--****  DROP TABLES
--*************************************
DROP TABLE NestedTrigger2, NestedTrigger1
GO

----------------------------------------