x

nested trigger problem

hi all

i make a trigger fire when an update happened in a table which contain self join relation as i want to update the current balance of the parent account and so on when an update happened in the last child this is the code that i wright

Create trigger [dbo].[UpdateAccounts] on [dbo].[ACCOUNT] after update as begin set nocount on; declare @value float;

select @value=inserted.CURRENT_BALANCE-deleted.CURRENT_BALANCE from deleted,inserted where deleted.ACCOUNT_ID=inserted.ACCOUNT_ID;

update dbo.ACCOUNT set dbo.ACCOUNT.CURRENT_BALANCE=dbo.ACCOUNT.CURRENT_BALANCE+@value from inserted where dbo.ACCOUNT.ACCOUNT_ID=inserted.PARENT_ID;

end;

any body can help thanks

more ▼

asked Mar 15, 2010 at 09:05 PM in Default

avatar image

user-1370
11 1 1 1

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

As a side note, take a moment to make your trigger multi-row update safe.

As it stands, if someone executes this:

update ACCOUNT set CURRENT_BALANCE=0 where ACCOUNT_ID IN ('1234','2345') 

you will work out a value for @value from one record and apply it to both (and their parents once you get nested triggers enabled)

more ▼

answered Mar 15, 2010 at 10:27 PM

avatar image

Mister Magoo
2.1k 2 5 8

(comments are locked)
10|1200 characters needed characters left

As Rob already answered here, the feature you want is controlled by a database option RECURSIVE_TRIGGERS.

Remember, that there is more to take in mind when using a recursive/nested triggers.

See BOL http://msdn.microsoft.com/en-us/library/ms190739%28SQL.90%29.aspxn on this topic.

You must think about the limits of the recursion etc. so the recursion needs to be designed quite well to not cause problems.

Also as Mister Magoo wrote here, you have to update your trigger code to be multi-row safe to be able to handle multi-row updates to the table as he mention.

more ▼

answered Mar 16, 2010 at 04:09 AM

avatar image

Pavel Pawlowski
22.7k 10 15 26

when i turned the RECURSIVE_TRIGGERS ON an exception happened in the run time Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

Mar 16, 2010 at 11:16 AM user-1370

@m - Sounds to me like you have an account which has its own account number as the parent. Perhaps you need "AND dbo.ACCOUNT.ACCOUNT_ID != inserted.ACCOUNT_ID"

Mar 16, 2010 at 11:28 AM Rob Farley
(comments are locked)
10|1200 characters needed characters left

Allowing triggers to call other triggers is a database option. Have a look at th Properties of your database to see if it's turned on.

more ▼

answered Mar 15, 2010 at 09:33 PM

avatar image

Rob Farley
5.8k 16 21 28

i want the trigger call himself as i want to make the update in the same table

Mar 15, 2010 at 09:43 PM user-1370

Yes - this is handled by the option I'm referring to.

Mar 16, 2010 at 04:16 AM Rob Farley

Try this in your trigger too - it'll make it work for multiple rows: update a set CURRENT_BALANCE=a.CURRENT_BALANCE+i.CURRENT_BALANCE-d.CURRENT_BALANCE from dbo.ACCOUNT as a JOIN inserted as i ON a.ACCOUNT_ID=i.PARENT_ID JOIN deleted as d ON d.ACCOUNT_ID = i.ACCOUNT_ID; Another option would be to make your balance change through a stored procedure which used a recursive CTE to list all the accounts that need changing.

Mar 16, 2010 at 04:19 AM Rob Farley

this code work like the one i make which make the update in the same level but i want the update effect in more than one level

Mar 16, 2010 at 11:19 AM user-1370

No - my query updates the table aliased as 'a', which is joined to inserted (i) on a.Account_Id = i.Parent_Id. So it's definitely updating the parent, not the original level.

Mar 16, 2010 at 11:24 AM Rob Farley
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x139

asked: Mar 15, 2010 at 09:05 PM

Seen: 1676 times

Last Updated: Mar 16, 2010 at 04:13 AM

Copyright 2016 Redgate Software. Privacy Policy