SQL Trigger

TRIGGER EXAMPLE

— TRIGGER TEST
— Essentially this trigger fires when an invoice is posted.
— If it exceeds the quantity available it gets backorded
— in the product table if ok, it deletes the quantity from
— the onhand value for that product.
Use BruceDBTest2

IF OBJECT_ID (‘InvoiceBackOrderUpdate’, ‘TR’) IS NOT NULL
DROP TRIGGER InvoiceBackOrderUpdate

Go

—— CREATE UPDATE TRIGGER
Create Trigger InvoiceBackOrderUpdate
On Invoice
After insert
as
Begin
Declare @InsertedQuan int
select @InsertedQuan = quan from inserted
Declare @ProductId int
select @ProductId = productid from inserted
Declare @OnHand int
select @onHand = QuantityOnHand from product where @ProductId = product.ProductId

if (@OnHand >= @InsertedQuan)
BEGIN
Print ‘In Stock’;
update product
set Product.QuantityOnHand = Product.QuantityOnHand – @InsertedQuan
where @productId = product.ProductId
–Throw 50001, ‘Came Out Of Stock’ ,0;
END
ELSE
BEGIN
Print ‘Insufficent quantity in stock’;
update product
set product.Backordered = Product.Backordered + @InsertedQuan
where @productid = product.productid;
Print ‘Item Backordered’;
–Throw 50000, ‘Insufficent quantity in stock’,0;
END
END
GO
——- GOOD TRIGGER
Insert into invoice values (2,’12/7/2016′,2,2)
Go

—- FAIL TRIGGER
Insert into invoice values (2,’12/8/2016′,4,2)
Go

select * from invoice where salesdate > ’12/6/2016′

GO

select * from product
–select * from invoice
–Go

— TEST UPDATE WITH CAL
— Update t2
–Set QuantityOnHand = QuantityOnHand – 1
–from Product t2 where t2.ProductName = ‘Monkey’