Need help writting 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)

Need help writting trigger

Author

Topic

dtrance
Starting Member

10 Posts

Posted - 2004-09-09 : 11:34:40

Hello everyone,

I need some help with updating quantity of an inventory table when a record is inserted into a table named transactions.

In the inventory table a quantity already exists along with the itemid. The end user enters the quantity they have taken and once submitted it is recorded in the transactiont able with the itemid and quantity taken out.

Once a record is inserted into the transactions table, I would like the quantity in the inventory table automatically reduced with the amount inserted into the transaction table.

This is all I have so far and I am stuck. Forgive my inexperience if this seems real elementary.

CREATE TRIGGER [updateqty] ON [dbo].[transactions]
FOR INSERT
AS

update inventory (quantity)

n/a
deleted

35 Posts

Posted - 2004-09-09 : 12:59:37

it would be like this

CREATE TRIGGER [updateqty] ON [dbo].[transactions]
FOR INSERT
AS

update inventory
set inventory.quantity = inventory.quantity - inserted.quantity
from inventory
join inserted
on inventory.itemnumber = inserted.itemnumber

I guessed on column names.

The big this to notice is the "inserted" table. this table is a temp table where MSSQL stores the values being inserted. the column names for this table are the same as the column names for the table you are inserting to. you can select from that table. I am joining it to your inventory table to get the correct item number updated.

HTH
Paul

chadmat
The Chadinator

1974 Posts

Posted - 2004-09-09 : 13:00:00

I didn't check the syntax, but this should work:


CREATE TRIGGER [updateqty] ON [dbo].[transactions] 
FOR INSERT
AS

update inventory
set quantity = i.quantity - ins.quantity
from inventory i join inserted ins on i.itemid = ins.itemid

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

dtrance
Starting Member

10 Posts

Posted - 2004-09-09 : 13:50:29

You guys are awesome! Thanks for taking the time to help me out.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources