nested trigger problem

Question

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

Answer 1

0

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)

answered Mar 15 '10 at 10:27 PM

Mister Magoo
450 ● 3

(comments are locked)

Answer 2

0

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.

answered Mar 16 '10 at 04:09 AM

Pavel Pawlowski
20.3k ● 5 ● 10 ● 20

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 '10 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 '10 at 11:28 AM Rob Farley

(comments are locked)

Answer 3

0

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.

answered Mar 15 '10 at 09:33 PM

Rob Farley
5.7k ● 13 ● 17 ● 19

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

Mar 15 '10 at 09:43 PM user-1370

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

Mar 16 '10 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 '10 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 '10 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 '10 at 11:24 AM Rob Farley

(comments are locked)

nested trigger problem

Follow this question

Related Questions