checking over my 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 Administration

checking over my trigger

Author

Topic

dlmagers10
Starting Member

48 Posts

Posted - 2010-12-03 : 08:38:07

Does this look like it is right? I am creating a trigger when inserting a row in the INVENTORY table, I need to add the ON_HAND value to the TOTAL_ON_HAND value for the BOOK table.

create trigger add_inventory
on inventory
after insert
as

update inventory
set on_hand(select on_hand from inserted),
where total_on_hand = (select total_on_hand from inserted);

Kristen
Test

22859 Posts

Posted - 2010-12-03 : 09:21:14

[code]Update U
set on_hand = I.on_hand,
FROM inventory AS U
JOIN inserted AS I
ON I.MyPK = U.MyPK
[/code]
but normally I would expect the formula to be something like:

QtyOnHand = QtyOnHand + StockAdditions - StockWithdrawls

rather than just setting the QtyOnHand from a single transaction in your add_inventory table.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-03 : 09:32:25

Is it possible to insert more than one row at a time into the inventory table - if not you should protect against it?
I would have thought the trigger would be on the book table rather than the inventory - book doesn't seem to be involved, is it the transaction table?

create trigger trxxx on book for insert
as
update inventory
set amt = i.amt + b.amt
from inventory i
join inserted b
on b.prodid = i.prodid

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

dlmagers10
Starting Member

48 Posts

Posted - 2010-12-03 : 09:39:12

I am thinking it is possible to insert more than one row at a time into the inventory table. You are right the book table is not so much involved once I really look at it. But then again the question is to retrieve and output the book_code, title, type, and price (all of which are in the Book Table). I dont think I started out first with the question. I was just trying to get some feedback on the answer that I came up with. Let me see...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources