Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Update trigger fires for only 1 record
Message
From
02/05/2006 15:14:38
 
 
To
All
General information
Forum:
Microsoft SQL Server
Category:
Other
Title:
Update trigger fires for only 1 record
Miscellaneous
Thread ID:
01118517
Message ID:
01118517
Views:
44
There is a table dspRunH. It has primary key field - PKey.
There is a child table dspRunD. It has foreign key field - RunH_FK which references dspRunH.PKey.
This child table has an update trigger:
ALTER TRIGGER [CheckOut_Update] ON [dbo].[dspRunD] 
FOR UPDATE
AS
SET NOCOUNT ON
DECLARE @Drv_CD	CHAR(4), @UserOut VARCHAR(25), @UserIn VARCHAR(25), @Invoice_No	CHAR(8)
DECLARE @COut BIT, @CIn	BIT, @FKey INT, @PKey INT
DECLARE @Route_CD CHAR(2), @Run_Time DATETIME, @Trans_Note VARCHAR(100)

SELECT @Invoice_No = Invoice_No, @COut = Checkout, @CIn = Checkin, @FKey = RunH_FK, @PKey = PKey
	FROM Inserted
print 'Trigger - ' + @Invoice_No
SELECT @Drv_CD = Drv_CD, @UserOut = CheckOut_User, @UserIn = CheckIn_User,
		@Route_CD = Route_CD, @Run_Time = Run_Time
	FROM dspRunH WHERE PKey = @FKey
SET @Trans_Note = 'Run ' + @Route_CD + ' ' + CAST(@Run_Time AS CHAR(20))

--Checkout transaction
IF UPDATE(Checkout)
	IF @COut = 1
		EXEC usp_dsp_Invoice_Trans @Invoice_No, 'CHECKOUT', @Drv_CD, @Trans_Note, @UserOut
	ELSE
		EXEC usp_dsp_Invoice_Trans @Invoice_No, 'CHECKOUT_UNDO', @Drv_CD, @Trans_Note, @UserOut

--Checkin transaction	
IF UPDATE(Checkin) AND @CIn = 1
	EXEC usp_dsp_Invoice_Trans @Invoice_No, 'CHECKIN', @Drv_CD, @Trans_Note, @UserIn
The dspRunH record with PKey = 236 has 3 child records in dspRunD table. So when I run this statement:
UPDATE dspRunD SET CheckOut = 1 where RunH_FK = 236
the trigger should fire 3 times, right?
But statement
PRINT 'Trigger - ' + @Invoice_No
prints only once. Why?
Next
Reply
Map
View

Click here to load this message in the networking platform