Creating triggers

Triggers are SQL statements which are stored with the intention of being activated or fired when an event associated with a database table occurs. This could be any event including an INSERT, UPDATE and DELETE. Lets begin by creating a few simple insert triggers

1. CREATE a trigger on the ORDERLINE table for the insert event. The trigger will update the corresponding PRODUCT record QTY_ORDERED by adding the value of QTY to QTY_ORDERED.

CREATE TRIGGER tr_qty_ordered_value_insert 
ON Orderline FOR INSERT
AS
BEGIN 
     UPDATE product 
      SET QTY_ORDERED =  QTY_ORDERED +
 ((SELECT qty from INSERTED) * (SELECT unitprice from INSERTED)) 
       WHERE product.ProductID = (SELECT ProductID from INSERTED);      
END;
Command(s) completed successfully.

2. CREATE a trigger on the ORDERLINE table for the delete event. The trigger will update the corresponding PRODUCT record QTY_ORDERED by subtracting the value of QTY FROM QTY_ORDERED.

CREATE TRIGGER tr_qty_ordered_value_delete 
ON Orderline FOR DELETE
AS
BEGIN 
     UPDATE product 
      SET product.QTY_ORDERED =  product.QTY_ORDERED -
 ((SELECT OrderLine.qty from orderline) * 
  (SELECT OrderLine.unitprice from orderline)) 
       WHERE product.ProductID = (SELECT ProductID from orderline);          
END;

Command(s) completed successfully.

3. CREATE a trigger on the ORDERLINE table for the update event. The trigger will update the corresponding PRODUCT record QTY_ORDERED by subtracting the OLD QTY and then adding the NEW QTY

CREATE TRIGGER tr_qty_ordered_value_update
ON Orderline FOR UPDATE
AS
BEGIN 
     UPDATE product
     SET QTY_ORDERED =  QTY_ORDERED -
      ((SELECT qty from DELETED) * (SELECT unitprice FROM DELETED))
            +
      ((SELECT qty FROM INSERTED) * (SELECT unitprice FROM INSERTED))
      WHERE product.ProductID = (SELECT ProductID from INSERTED);
END;

 Command(s) completed successfully.