AdventureWorks Inventory Trigger

Question

My inventory needs limitations. I am supposed to make sure my inventory quantity never exceeds 1000 for my main warehouse. All excess units are sent to an external storage site and tracked separately. I need to write a trigger for the "ProductInventory" table to make sure inventory cannot exceed 1000 units when there is an update.

CREATE TRIGGER tgrExcessInventory

ON production.productinventory

AFTER UPDATE

AS

BEGIN

(This is where I get lost in the sql statement I need to make to ensure my limit is not exceeded.)

What sort of language do I need to make this trigger cap at 1000?

Hint: `BEFORE UPDATE`. *After* the update is too late. – Gordon Linoff Apr 24 '17 at 23:40 — Gordon Linoff, Apr 24 '17 at 23:40

score 2 · Answer 1 · answered Aug 05 '17 at 14:56

Probably a little late, but try this:

CREATE TRIGGER tgrExcessInventory
on Production.ProductInventory
FOR UPDATE
AS
IF EXISTS
 (SELECT 'True'
 FROM Inserted i
 JOIN Deleted d
  ON i.productID = d.ProductID
  AND i.locationID = d.LocationID
  WHERE (d.quantity + i.quantity) >= 1000 OR 
  i.quantity >=1000
 )
 BEGIN
  RAISERROR('Cannot increase stock where units would be over 1,000 units',16,1)
  ROLLBACK TRAN
 END

The code here will fire the trigger is there is a scenario in which you see set quantity = quantity + whatever value would bring it to 1000 or more, and also fire it there's a direct set quantity = 1000+

score 0 · Answer 2 · edited May 23 '17 at 12:02

0

Are you sure you have to check it on UPDATE? As far as my logic goes when you`re updating a value it means that the values exists hence the inventory quantity is not modified. You might want to created a trigger for INSERT

As far as the check for the inventory quantity you can do a simple count on your table.

IF (SELECT COUNT(1) FROM production.productinventory) >= 1000
BEGIN
    //Do your thing
END

You should check This question for some more answers. Unfortunately MSSQL does not support "BEFORE UPDATE" as Gordon Linoff mentioned in the comments but you can easily work with the DELETED and INSERTED tables in the trigger.

I hope it helps! Cheers!

edited May 23 '17 at 12:02

Community

1
1

answered Apr 25 '17 at 08:12

Cosmin

152
3
18

I assumed an UPDATE because of the e-mail I received from the higher ups. "We need to monitor when an update will exceed this boundary so it can be addressed in the production meetings." We arent inserting new rows as we get more inventory or sell inventory. We just have a running calculation that decrements when we sell and increases when we purchase. So we have 980 of Part#111, purchase 25 more for an updated total of 1005. I need to make sure that order cannot be placed as it will put us over the limit. Should I be placing a RAISERROR here when they try to order excess? – Logan Apr 25 '17 at 16:47
@Logan That makes it more clear now. Is the update done inside of a stored procedure? If yes then it would be a lot better to check the values in that procedure before even triggering the update. If not you could check this [question](http://stackoverflow.com/questions/9507012/how-to-prevent-updates-to-a-table-with-an-exception-for-one-situation) , and check the trigger types [here](http://www.dotnettricks.com/learn/sqlserver/different-types-of-sql-server-triggers). Hope it helps! – Cosmin Apr 26 '17 at 14:00

AdventureWorks Inventory Trigger

2 Answers2