Adding a field in a 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

General SQL Server Forums

New to SQL Server Programming

Adding a field in a trigger

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-05-30 : 15:13:00

Have a table with the following data.

Table1
Ord_no inv_no amt Account freefield3
25 125 100.00 1444
25 125 10.00 3555 Rebate
25 125 10.00 3555 Rebate

After the records are inserted I need to add the amt's where freefield3 equals rebate then subtract that sum from the amt where account = 1444. So my final result would be to have 80.00 in the amt field where account equals 1444

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-30 : 15:17:12

UPDATE Table1
SET amt = amt - (SELECT SUM(amt) FROM Table1 WHERE freefield3 = 'Rebate')
WHERE Account = 1444

EDIT: Just saw your subject, you'll probably want to use the inserted trigger table instead of Table1 in the subquery.

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

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-05-30 : 15:34:21

I put that code in and it subtracted twice from the original amt.

begin
UPDATE gbkmut
SET gbkmut.bdr_hfl = gbkmut.bdr_hfl - (SELECT SUM(gbkmut.bdr_hfl) FROM inserted join gbkmut on
inserted.bkstnr_sub = gbkmut.bkstnr_sub and inserted.faktuurnr = gbkmut.faktuurnr
WHERE inserted.freefield3 = 'Rebate')
WHERE reknr = ' 1040'
end

There were two records it had to add together.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-30 : 17:57:20

Why did you add a join in the subquery?

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-31 : 00:42:29

quote:
Originally posted by Vack

Have a table with the following data.

Table1
Ord_no inv_no amt Account freefield3
25 125 100.00 1444
25 125 10.00 3555 Rebate
25 125 10.00 3555 Rebate

After the records are inserted I need to add the amt's where freefield3 equals rebate then subtract that sum from the amt where account = 1444. So my final result would be to have 80.00 in the amt field where account equals 1444

May be this:-

CREATE Trigger Table1_I ON Table1
AFTER INSERT 
AS
BEGIN
UPDATE t1
SET t1.amt=t1.amt-t2.TotalRebate
FROM Table1 t1
INNER JOIN INSERTED i
ON i.Ord_no =t1.Ord_no
AND i.inv_no=t1.inv_no
INNER JOIN (SELECT Ord_no,inv_no,SUM(amt) AS TotalRebate
            FROM Table1
            WHERE freefield3='Rebate'
            GROUP BY Ord_no,inv_no) t2
ON t2.Ord_no =t1.Ord_no
AND t2.inv_no=t1.inv_no
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources