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 '10 at 09:05 PM in Default

user-1370 gravatar 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 '10 at 10:27 PM

Mister Magoo gravatar image

Mister Magoo
1.6k 1 3 5

(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 '10 at 04:09 AM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.2k 8 11 21

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)
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 '10 at 09:33 PM

Rob Farley gravatar image

Rob Farley
5.7k 15 18 20

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)
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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x114

asked: Mar 15 '10 at 09:05 PM

Seen: 1433 times

Last Updated: Mar 16 '10 at 04:13 AM