Nested Triggers - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Nested Triggers

Author

Topic

Kristen
Test

22859 Posts

Posted - 2011-08-24 : 10:59:20

Can I have two triggers on a table which both update the underlying table?

I'm getting


Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

and just want to know if its the UPDATE of the underlying table. If not I'll go looking for something else

(I think there is a possibility that the trigger is updating table2, and the trigger for Table2 is also updating Table1 - so that might be the cause)

Its all been working fine until I added a second trigger on Table1 though ..

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-08-24 : 11:04:49

sounds like trigger firing another trigger firing another trigger issue. might be stuck in a loop

If you don't have the passion to help people, you have no passion

Kristen
Test

22859 Posts

Posted - 2011-08-24 : 11:07:46

"sounds like trigger firing another trigger firing another trigger issue"

I thought (might be a dangerous thing!) that updating the underlying table, from a trigger, did not fire the [other] trigger(s) on that table?

I'll go read BoL ...

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-08-24 : 11:47:28

trigger is a trigger unless you disable it

If you don't have the passion to help people, you have no passion

Kristen
Test

22859 Posts

Posted - 2011-08-24 : 12:39:01

Trigger1_A on Table1 does:


UPDATE T1
SET ...
FROM inserted AS I
    JOIN Table1 AS T1
        ON T1.ID = I.ID

Trigger1_B on Table1, and set to run LAST does:


UPDATE U
SET ...
FROM inserted AS I
    JOIN MyTable AS U
        ON U.ID = I.ID

UPDATE T2
SET ...
FROM inserted AS I
    JOIN Table2 AS T2
        ON T2.ID = I.ID

UPDATE T3 -- Stock Level Adjustments
SET ...
FROM deleted AS I
    JOIN Table3 AS T3
        ON T3.ID = I.ID

UPDATE T3
SET ...
FROM inserted AS I
    JOIN Table3 AS T3
        ON T3.ID = I.ID

However, Table2 has a trigger which does:


UPDATE T2
SET ...
FROM inserted AS I
    JOIN Table2 AS T2
        ON T2.ID = I.ID

INSERT INTO Table4
SELECT Col1, Col2, ...
FROM inserted AS I
WHERE NOT EXIST ...

UPDATE T4
SET ...
FROM inserted AS I
     JOIN Table4 AS T4
         ON T4.ID = I.ID

the triggers on Table3, 4 ... all have


UPDATE X
SET ...
FROM inserted AS I
    JOIN MySelf AS X
        ON X.ID = I.ID

but do not update any tables other than themselves.

So it seems that the Trigger1_A and Trigger1_B updating their own table are recursively calling the other trigger on that table?

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-08-24 : 14:38:57

the key indicator is view nesting level exceeded (limit 32) because it does not seem like you have 32 tables right? So in order to save you from recursive loopti loops, SQL stops you on 32nd iteration and saves you from eating straw hats :)

If you don't have the passion to help people, you have no passion

Kristen
Test

22859 Posts

Posted - 2011-08-24 : 17:40:30

Seems that if I have two triggers on a table, that each update the underlying table, then it is recursive.

Possibly using



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

will fix this. It certainly stops the other trigger(s) being called when the first trigger updates the underlying table, but I haven't had time to test, as yet, whether it stops the triggers on other tables which are inserted/update fire their triggers, or not.

Kristen
Test

22859 Posts

Posted - 2011-08-25 : 02:51:32

OK, I've now tried that and it prevents triggers firing on other tables that the trigger alters.

So cannot have a trigger alter the underlying table if there is more than one trigger on the table.

Kristen
Test

22859 Posts

Posted - 2011-08-25 : 04:45:32

So ... I could use TRIGGER_NESTLEVEL() to limit the number of levels that I allow the triggers to "nest" to. No idea what value that should be. Mostly the trigger will be called because of a direct update on the table. Its possible that it will be called by a trigger on another table.

Or I could set some sort of Flag on the record, so that the other trigger only updates depending on the value of the flag. Strikes me as "fraught". If one of the triggers is ever dropped the other may never fire (because Flag value is not as-expected)

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-08-25 : 11:30:33

that is why I try my darnest to avoid using triggers if at all possible. I like to have control over what happens. whatever the triggers are doing I do it myself in my tsql code. That way I have control. I am a control freak

If you don't have the passion to help people, you have no passion

Kristen
Test

22859 Posts

Posted - 2011-08-25 : 13:12:53

Trouble is ...

We do everything through SProcs. More or less. But several Sprocs may write to a table, and it then becomes harder to encapuslate the logic.

Mostly our triggers just set the UpdateDate, and save a copy of the DELETED into an Audit Table.

But the Order Header and Order Detail tables' triggers do the logic for checking prices, running totals,a nd so on. Not the heavy stuff the Sprocs do, but the things that MUST BE RIGHT. The DBAs will do a quickie UPDATE on tables, now and again. Its handy that the Trigger is there to do some safeguarding - to save the AUDIT record, if nothing else.

But because we write back the core table (to set the Update date) then two triggers per table is not going to work (well, I can't see a way to do it safely).

What I want is that while a trigger is running on TableX there is no trigger for any changes to TableX itself (by that trigger).

If my Trigger writes to TableY and the trigger on TableY also writes to TableX then I'd like that to fire the TableX triggers.

But I don't want an update of TableX, by Trigger1 on TableX, to fire Trigger2, 3, 4 on TableX - they are going to fire anyway.

But I can't see that that is possible.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-08-25 : 13:16:28

Why not combine the two triggers (on table1) into a single trigger?

Be One with the Optimizer
TG

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-08-25 : 13:32:30

It seems as if you have two issues:

1. A Process Problem - If you say you need triggers to keep your data correct because DBAs will go in and update tables directly. That's a process issue. How you deal with that is a whole other matter and maybe triggers are one method of dealing with that.

2. A Design Problem - Sounds like you have some complicated logic. But, using triggers for this sort of logic sounds like an issue to me. It's one way to attack the problem, but, as you've found out, it's problematic to use triggers like this.

I'm probably not telling you anything you don't already know. But, my point is that you (the preverbal you) need to take a step back and look at the design. I feel your pain

and I assume you're, unfortunately, not in a position to redesign things, but you will need to map things out in order to see all the issues and apply "fixes" like TRIGGER_NESTLEVEL and such. Unless you are in a position to change the design, then you can fix this up.

Kristen
Test

22859 Posts

Posted - 2011-08-25 : 15:20:22

"Why not combine the two triggers (on table1) into a single trigger?"

Yup, that works, and indeed is what we have done for now.

The benefit of multiple triggers would be that the Client-Specific code would requiresno modification to our Core Application code (which is intended to be identical for all our clients)

@Lamprey:

1) Lets not get it out of perspective. It happens rarely. If it happens the DBAs are probably under-the-gun to fix something, if the Trigger audits their changes, or forces an UpdateDate into the LastUpdated column, it saves the DBA having to be careful to "do it right" - which, I would require, would need a dress-rehearsal etc., and when this happens the client is climbing the walls and screaming that "there aint time for a dress rehearsal".

But other than that I agree. We don't have code in the triggers in order to allow DBAs to be sloppy .. just code to save them from themselves.

2) On 99% of tables we have very "light" logic in triggers:

Set the UpdateDate
Save the DELETED to Audit table
Maybe enforce that some/all columns that equal "" are set to NULL instead (as a house-rule we do not allow "" as a value for most varchar columns)

Occasionally we have complex logic in triggers because the manipulative Sprocs etc are numerous and there is a risk that they get out of step. its 5%, or less, of our triggers that have such complexity.

But your point is definitely valid

"take a step back and look at the design"

This only ever happens where we make the assumption that "We can just do X" to accommodate a client. That's the case here and ... lo & behold ... we are hosed!

The client wants to enforce that if a user has a Back Order'ed Order Item then the price from when the back ordered it will prevail (if current price is higher).. Its the most simple of simple UPDATEs to perform. But where to put that update to faciliate the requirement is the challenge.

Also, because we are then operating outside our comfort zone and familiarity, we don't even know if there is considered-knowledge about how to do this thing - hence I am here, asking!.

We've been in the same hole, digging furiously, with Computed Columns, Indexes on Views with certain complexities, Instead-of-Triggers ... in the past. We've banned them all as not suiting us. Looks like "just add a trigger with the Client's bespoke logic" will be in the same bin by the end of the week ..

The alternative (given the design of our application) is that we trap every possible point in the application where this table could change, and add a "configurable option" to allow client-specific logic to be added (that would be painful to implement), or we change the whole application to allow customised Sprocs to be used (with all the code management that that entails). Customer-specific mods are as rare as hens' teeth for us - we build their requirements into the application (as "configurable options" instead). SO we're well outside our comfort zone in trying to accommodate this particular client requirement.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-08-25 : 18:33:12

pretty tough environment to work in (which seems to be everywhere nowadays) when it comes to database. Clients have no idea of the impact of their fickle demands though necessary at times. especially places that started out small and changes like that were easy with little impact. but as things grow that kind of approach is not sustainable.

"take a step back and look at the design" is a very valid point indeed!!!

But like you I also keep audit on 100s of tables with thousands of columns without triggers. It is doable especially for very few columns. Also have you looked into constraints on the UpdateDate columns using

DEFAULT (suser_sname()) FOR [CreatedDate]

?

I think with a good design you could accommodate both the "there aint time for a dress rehearsal" and sanity check for you.

If you don't have the passion to help people, you have no passion

Kristen
Test

22859 Posts

Posted - 2011-08-26 : 03:07:23

Sorry, I haven't understood your point on:

"constraints on the UpdateDate columns using
DEFAULT (suser_sname()) FOR [CreatedDate]"

and I'd be interested in how you do audit tables without triggers (I mean, how you ensure that no fraudulent, or accidental, behaviour bypasses the audit.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-08-26 : 10:32:54

1. you audit them by adding audit code in your INSERT/UPDATE/DELETE tsql code. In your underlying tables you add CreatedDate, CreateBy, UpdatedDate, UpdatedBy, TimeStamp (overkill ?) columns with each having default constraints.

2. When doing INSERT/UPDATE/DELETE you add code that dumps
inserted.fieldName
to your audit tables
3. your audit table should be a clone of your main table plus an action nvarchar() field that will show capture INSERT/UPDATE/DELETE via $action
4. you can either dump directly into your audit table or dump the [INSERT/UPDATE/DELETE]d row(s) to table or temp variable
5. if you wish you can even add code that will take a snapshot of the row(s) that will be UPDATEd. pre/post update
6. disallow any and all direct table data manipulation especially in production that is ridiculous no/no. Make that a policy
7. if there are any changes to be made have them done via scripts. in these scripts add the necessary auditing mentioned above.
here are basic sample constraint fields


ALTER TABLE [dbo].[Kristen] ADD  CONSTRAINT [DF_Kristen_CreatedAt]  DEFAULT (getdate()) FOR [CreatedAt]
GO

ALTER TABLE [dbo].[Kristen] ADD  CONSTRAINT [DF_Kristen_CreatedBy]  DEFAULT (suser_sname()) FOR [CreatedBy]
GO

ALTER TABLE [dbo].[Kristen] ADD  CONSTRAINT [DF_Kristen_ChangedAtc]  DEFAULT (getdate()) FOR [ChangedAt]
GO

ALTER TABLE [dbo].[Kristen] ADD  CONSTRAINT [DF_Kristen_ChangedBy]  DEFAULT (suser_sname()) FOR [ChangedBy]
GO

ALTER TABLE [dbo].[Kristen] ADD  CONSTRAINT [DF_Kristen_ChangedApp]  DEFAULT (app_name()) FOR [ChangedApp]
GO

If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources