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
- 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 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 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_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.
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.
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.
Comments