Triggers not cascading

Dev Shed Forums Sponsor:

August 5th, 2004, 11:28 AM

sn1p3t

Registered User

Join Date: Jun 2004

Posts: 14

Time spent in forums: < 1 sec
Reputation Power: 0

Triggers not cascading

Here's my table setup:

tblSteps:
StepID int IDENTITY
ParentStepID int ALLOWSNULLS
OtherID int
Amount money

tblOther:
OtherID int IDENTITY
Amount money

Now, I have a trigger defined on tblSteps:

Code:

CREATE TRIGGER tgrUpdateAmount
ON dbo.tblSteps
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
	DECLARE @Amount money
	SET @Amount = ISNULL((SELECT SUM([Amount]) FROM inserted), 0) - ISNULL((SELECT SUM([Amount]) FROM deleted), 0)

	IF (SELECT [ParentStepID] FROM inserted) IS NULL
	BEGIN	
		UPDATE tblOther SET [Amount] = [Amount] + @Amount WHERE [OtherID] = (SELECT [OtherID] FROM inserted)
	END
	ELSE
	BEGIN
		UPDATE tblSteps SET [Amount] = [Amount] + @Amount WHERE [StepID] = (SELECT [ParentStepID] FROM inserted)
	END
	
END

What this code basically does is this: if you update the amount of a Step that has a ParentStepID, it will take what was addded (or deleted etc...) and update it's parent with the added amount. If the Step does not have a ParentStepID, it will take the amount add add it to the Other row it corresponds to.

Here's an example of some test data:
tblOther:
OtherID: 1
Amount: 0

tblSteps:
StepID: 1
ParentStepID: NULL
Amount: 0
------
StepID: 2
ParentSTepID: 1
Amount: 0

If I update the Amount to 100 for StepID=2, it also updates the amount for StepID=1. If I update the amount for StepID=1, it also updates the amount for OtherID=1. However, when I update the amount for StepID=2, it does not cascade up to the tblOther level (e.g. Updating StepID=2 should update StepID=1 which should update OtherID=1, right?)

Basically, the trigger isn't cascading. Can anyone point out what I need to do?

Thanks

August 5th, 2004, 01:40 PM

swampBoogie

Contributing User

Dev Shed Intermediate (1500 - 1999 posts)

Join Date: Jan 2003

Location: Paris Uppland

Posts: 1,784

Time spent in forums: 2 Weeks 4 Days 21 h 42 m 49 sec
Reputation Power: 37

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_da-di_8c32.asp

look for recursive triggers

Viewing: Dev Shed Forums > Databases > MS SQL Development > Triggers not cascading

« Previous Thread | Next Thread »

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox

Forum Jump