February 21, 2011 at 9:17 am
Hi,
I have been asked to create a trigger that removes/changes a flag on a table based on an insert into another table. The flag changes the status from H to blank if the transaction is a negative number.
The join is made on a LotNumber. Question is how do i only change the status the of the inserted LotNumber and not all status's of all LotNumbers?
Here's what i have...
update TargetTable Tgt
set StatusFlag = ' '
Left Join TransactionTable Trn
on Tgt.LotNumber = Trn.LotJob and Tgt.ReceiptQty = Trn.TrnQuantity
where Trn.TrnQuantity < 0
..which i guess will update all lines in the target table where there is a match with a value of less than 0 but i only want to update my one LotNumber. i.e. AND Tgt.LotNumber = '00027089'
Can anyone point me in the right direction.
Thanks
February 21, 2011 at 9:48 am
SET ANSI_NULLS, QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER dbo.TI_TransactionTable
ON dbo.TransactionTable
AFTER INSERT
AS
SET NOCOUNT ON
UPDATE dbo.TargetTable
SET StatusFlag = ' '
WHERE StatusFlag <> ' '
AND EXISTS
(
SELECT *
FROM inserted I
WHERE I.LotJob = dbo.TargetTable.LotNumber
AND I.TrnQuantity = dbo.TargetTable.ReceiptQty
AND I.TrnQuantity < 0
)
GO
February 22, 2011 at 5:30 am
perfect, thanks ken. 🙂
March 1, 2011 at 4:05 am
Hi
I have created the following from the posts below...
UPDATE EncoreCompanyT.dbo.DataLots
SET StatusFlag = ' '
WHERE StatusFlag <> ' '
AND EXISTS
(
SELECT *
FROM (Select LotJob, TrnQuantity * -1 as TrnQuantity, TrnQuantity as NegVals
From EncoreCompanyT.dbo.LotTransactions
Where TrnValue < 0
and TrnType = 'R') I
JOIN EncoreCompanyT.dbo.DataLots DL
ON I.LotJob = DL.LotNumber
AND I.TrnQuantity = DL.ReceiptQty
)
...Which is a trigger on the dbo.LotTransactions to set a flag in dbo.DataLots to ' ' instead of 'H'.
The AND EXISTS (SELECT *.....) bit pulls back 2500'ish rows when all i actually want is to returnthe last row that was inserted into the dbo.LotTransactions table. The table doesn't have a UNIQUE field??
The other thing is that when i run the update it sets ALL status flags (100k'ish) in my dbo.DataLots table to ' ' not just the ones returned (2500'ish rows) in the select statement??
March 1, 2011 at 4:26 am
Ok, after some meddling i've managed to get ....
UPDATE DL
SET StatusFlag = ' '
FROM EncoreCompanyT.dbo.DataLots DL
JOIN (Select LotJob, TrnQuantity * -1 as TrnQuantity
From EncoreCompanyT.dbo.LotTransactions
Where TrnValue < 0
And TrnType = 'R') LT
ON LT.LotJob = DL.LotNumber
AND LT.TrnQuantity = DL.ReceiptQty
WHERE StatusFlag <> ' '
...to update the 2500ish rows in the dbo.DataLots table. I now only need to update the single row that was inserted into the dbo.LotTransactions table??
Can anyone tell me how to update the single row?
Thanks.
March 1, 2011 at 6:10 am
In your sub-query, use "inserted" instead of "EncoreCompanyT.dbo.LotTransactions". In triggers, you have two "tables" called "inserted" and "deleted". "deleted" has the "before" data, "inserted" has the "after" data. For an insert, there's no "before" data, but there is "after" data, so you can use it to only work on the rows that you are currently adding.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 1, 2011 at 8:03 am
ALTER TRIGGER dbo.trg_LotTransactions
ON EncoreCompanyT.dbo.LotTransactions
FOR INSERT
AS
SET NOCOUNT ON
UPDATE DL
SET StatusFlag = ' '
--SELECT *
FROM EncoreCompanyT.dbo.DataLots DL
JOIN (Select LotJob, TrnQuantity * -1 as TrnQuantity
From INSERTED --EncoreCompanyT.dbo.LotTransactions
Where TrnValue < 0
And TrnType = 'R') LT
ON LT.LotJob = DL.LotNumber
AND LT.TrnQuantity = DL.ReceiptQty
WHERE StatusFlag <> ' '
Is this right?? I'm getting an error : Invalid Object Name : INSERTED???
March 1, 2011 at 8:30 am
It may need to be lower-case. I'm not sure. Other than that, it looks okay.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 1, 2011 at 8:57 am
just re-tested and it works fine. Thanks for all your help.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply