February 14, 2012 at 5:23 am
Hi,
I have two SQL tables which are referenced within a trigger. I have tested the trigger in a 'development' environment, but scaling up, it might be the cause behind a process or table 'lock' due to the data set.
The T-SQL is:
USE TESTDB
GO
CREATE TRIGGER [dbo].[ust_UpdateStatusRecord]
ON VCTM
AFTER UPDATE
AS
--PRINT 'After Trigger'
--RAISERROR('Error',16,1);
--ROLLBACK TRAN;
BEGIN
SET NOCOUNT ON;
INSERT INTO [dbo].[KWTM]
(
DataAreaId,
MovementId,
PlannedCollectFromDate,
PlannedCollectToDate,
PlannedDeliverFromDate,
PlannedDeliverToDate,
PlannedQty
)
SELECT
upd.DataAreaId,
upd.VCTMOVEMENTID,
upd.VCTPLANNEDCOLLECTFROMDATE,
upd.VCTPLANNEDCOLLECTTODATE,
upd.VCTPLANNEDDELIVERFROMDATE,
upd.VCTPLANNEDDELIVERYDATE,
upd.VCTPLANNEDQTY
FROM dbo.VCTM upd
WHERE
upd.DATAAREAID = 'ktl'
AND
upd.VCTMOVEMENTSTATUS = 1 --Delivered
SET NOCOUNT OFF;
The trigger is dependent on the upd.VCTMovementStatus being updated to '1' (if my code is correct). I need the KWTM table to be updated where the upd.VCTMovementStatus value has specifically changed from '0' to '1' ONLY.
Any ideas please. Sorry, I'm new to triggers!
Thanks in advance,
Neal
February 14, 2012 at 6:02 am
As it stands, your trigger is inserting all rows from the VCTM table that match the criteria into the KWTM table, not just those that have been updated by the statement that caused the trigger to fire.
Unless you have strange business requirements, I suspect your SELECT statement should be retrieving rows from the "updated" table...
SELECT...
upd.VCTPLANNEDQTY
FROM updated upd
WHERE
February 14, 2012 at 7:10 am
Hi Ian,
'No' we don't have strange business requirements, and as I pointed out, I am new to triggers,
but I did attempt to post/describe my T-SQL issue as well as I could.
I tried your solution, but I got the following error
Invalid object name 'UPDATED'.
Unfortunately, I still can't see where I compare the VCTMOVEMENTSTATUS value updating from '0' to '1'.
Thanks for your help anyway,
Neal
February 14, 2012 at 7:20 am
Try using the inserted table.
I do not think that Ian was taking the **** with his 'strange requirements' comment, just helpful.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 14, 2012 at 7:21 am
Doh... I didn't mean updated. I meant "inserted"
The trigger will give you access to 2 pseudo tables... deleted (which contains the contents of the rows before the update) and inserted (which contains the contents of the rows after the update).
You can construct you insert query as a regular SQL statement, joining the deleted and inserted tables, probably something like this...
INSERT INTO [dbo].[KWTM]
(
DataAreaId,
MovementId,
PlannedCollectFromDate,
PlannedCollectToDate,
PlannedDeliverFromDate,
PlannedDeliverToDate,
PlannedQty
)
SELECT
upd.DataAreaId,
upd.VCTMOVEMENTID,
upd.VCTPLANNEDCOLLECTFROMDATE,
upd.VCTPLANNEDCOLLECTTODATE,
upd.VCTPLANNEDDELIVERFROMDATE,
upd.VCTPLANNEDDELIVERYDATE,
upd.VCTPLANNEDQTY
FROM inserted upd
INNER JOIN deleted del on upd.(insert your key columns here) = del.(key columns)
WHERE
upd.DATAAREAID = 'ktl'
AND
upd.VCTMOVEMENTSTATUS = 1 --Deliver
AND
del.VCTMOVEMENTSTATUS = 0
February 14, 2012 at 7:24 am
Many, Many Thanks guys
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply