Help with Trigger - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Help with Trigger

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-05-29 : 16:07:02

Need to update a field on a record that is getting inserted based off of an existing record in table.

if the record being inserted has a value of Rebate in freefield3 then update bcode with bcode from another record where the other record has teh same order number and invoice number as the inserted record and also the existing record as an account number of 1040.

Here is what I have so far and it doesn't seem to be working.

CREATE TRIGGER [UPDATEEGBKMUTREBATERECORDS] ON [dbo].[GBKMUT]
AFTER INSERT
AS

BEGIN
UPDATE GBKMUT
SET GBKMUT.bcode = GBKMUT.bCODE
FROM INSERTED JOIN GBKMUT ON INSERTED.ord_no = GBKMUT.ord_no AND
INSERTED.inv_no = GBKMUT.inv_no
WHERE INSERTED.FREEFIELD3 = 'Rebate' AND INSERTED.ord_no = GBKMUT.ord_no AND
INSERTED.inv_no = GBKMUT.inv_no
END

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-29 : 16:16:28

I think you need to join to GBKMUT again.

Maybe this:

UPDATE g1
SET bcode = g2.bCODE
FROM GBKMUT g1
INNER JOIN inserted i
ON i.ord_no = g1.ord_no AND i.inv_no = g1.inv_no
INNER JOIN GBKMUT g2
ON i.ord_no = g2.ord_no AND i.inv_no = g2.inv_no
WHERE i.FREEFIELD3 = 'Rebate' AND g1.AccountNumber = 1040

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources