I want some Moore

Blog about stuff and things and stuff. Mostly about SQL server and .Net
posts - 219, comments - 2287, trackbacks - 33

My Links

Advertisement

News

Hi! My name is 
Mladen Prajdić  I'm from Slovenia and I'm currently working as a .Net (C#) and SQL Server developer.

I also speak at local user group meetings and conferences like SQLBits and NT Conference
Welcome to my blog.
SQL Server MVP

My Books

SQL Server MVP Deep Dives 2
The Red Gate Guide to SQL Server Team based Development Free e-book

My Blog Feed via Email
Follow MladenPrajdic on Twitter


Users Online: who's online

Article Categories

Archives

Post Categories

Cool software

Other Blogs

Other stuff

SQL stuff

Help in getting nested triggers from your database

if you don't know what nested trigers are read here.

Triggers are nested when a trigger performs an action that initiates another trigger.

So if you have 2 table where each has an after update trigger like:

CREATE TABLE trTest1 
(
id INT, 
NAME VARCHAR(50)
)
GO
INSERT INTO trTest1
SELECT 1, 'NAME 1' UNION ALL
SELECT 2, 'NAME 2' UNION ALL
SELECT 3, 'NAME 3' UNION ALL
SELECT 4, 'NAME 4'

GO
CREATE TABLE trTest2
(
id INT, 
NAME VARCHAR(50)
)
GO
INSERT INTO trTest2
SELECT 1, 'NAME 1' UNION ALL
SELECT 2, 'NAME 2' UNION ALL
SELECT 3, 'NAME 3' UNION ALL
SELECT 4, 'NAME 4'
GO
CREATE TRIGGER trigTest1 ON trTest1 FOR UPDATE AS

UPDATE t2
SET    id = i.id + 1
FROM trTest2 t2 JOIN inserted i ON t2.id = i.id

GO
CREATE TRIGGER trigTest2 ON trTest2 FOR UPDATE AS
UPDATE t1
SET    id = i.id + 1 
FROM trTest1 t1 JOIN inserted i ON t1.id = i.id
GO

SELECT * FROM trTest1
SELECT * FROM trTest2
GO

UPDATE trTest1
SET id = 10
WHERE id = 1

SELECT * FROM trTest1
SELECT * FROM trTest2
GO

DROP TABLE trTest1
DROP TABLE trTest2

If you run this it will fail with the eror message

Msg 217, Level 16, State 1, Procedure trigTest2, Line 2 Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

You have to use

EXEC sp_configure 'nested triggers', '0';
RECONFIGURE

to disable nested triggers execution.

 

I recently had to check one of our db's if there are any nested triggers in it.

so i wrote this script:

CREATE PROCEDURE usp_GetRefferencingTriggers 
AS
-- get all triggers and tables they're on.
SELECT     NAME AS TriggerName, OBJECT_NAME(parent_obj) AS ParentTable, c.TEXT
INTO    #AllTriggers
FROM    sysobjects o
        JOIN syscomments c ON o.id = c.id
WHERE    xtype = 'TR'

SELECT     t1.TriggerName AS MainTriggerName, t1.ParentTable AS MainTriggerParentTable, 
        --t1.TEXT AS MainTriggerText,
        t2.TriggerName AS TriggerReferencingParentTable, t2.ParentTable AS ReferencingTriggerParentTable 
        --t2.TEXT AS RefferencingTriggerText
FROM    #AllTriggers t1
        JOIN
        #AllTriggers t2 ON 
                t2.ParentTable <> t1.ParentTable
                AND t2.TEXT like '% ' + t1.ParentTable + '[(| ' + char(10) + '|' + char(13) + ']%'
order by t1.TriggerName, t2.ParentTable

DROP TABLE #AllTriggers
GO

It's not perfect but it reduces the number of triggers to look into a lot.

It simply looks for any triggers that have a current trigger's parent table name in their body.

So this means that it will also return rows for triggers in which you join a parent table to another table etc...

 

It's not perfect but it helped me in reducing number of triggers i had to check from 90 to 6.

And that's OK with me :))

 

Print | posted on Friday, February 09, 2007 8:32 PM | Filed Under [ SQL Server ]

Feedback

Gravatar

# re: Help in getting nested triggers from your database

I modified,
SELECT O.NAME AS TriggerName, OBJECT_NAME(parent_obj) AS ParentTable, C.TEXT
to:
SELECT O.NAME AS TriggerName, OBJECT_NAME(parent_obj) AS ParentTable, CAST(C.TEXT AS VARCHAR(4000)) AS [TEXT]

Why? To accomodate for the error "Cannot create a row of size 8091 which is greater than the allowable maximum of 8060".

Since SysComments.text is NVarChar(4000), and since triggers spaning multiple sysComments creates larger than 8060 byte records, we get the error for larger triggers only. Casting it to VarChar(4000) fixes this problem.



2/11/2007 9:45 PM | RAM
Gravatar

# re: Help in getting nested triggers from your database

Those are some mighty long trigger bodies you have there :))
Thanx for the tip.
2/12/2007 11:24 AM | Mladen
Gravatar

# re: Help in getting nested triggers from your database

I have a requirement to discover the name of the table the current trigger is executing against. I can do this, as long as the triggers aren't nested by way of this statement:

SELECT object_name(resource_associated_entity_id) FROM sys.dm_tran_locks WHERE request_session_id = @@spid and resource_type = 'OBJECT'

Is it possible to find the 'current' table name within a nested trigger ?
4/1/2008 10:44 AM | Jack
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET