The TRIGGERS are the event driven database programs which are triggered automatically by the database. The TRIGGERS are created using the CREATE TRIGGER statement.
For example, we want to create a TRIGGER which will update ORDER_COMMISION column of the ORDERS table to 5% of the ORDER_TOTAL value after every new record insertion in ORDERS table.
CREATE TRIGGER ORDERCOMMUPD AFTER INSERT ON ORDERS FOR EACH ROW MODE DB2SQL BEGIN ATOMIC UPDATE ORDERS SET ORDER_COMMISION=(5*ORDER_TOTAL)/100;
Using the above statement, we have created an AFTER trigger which will be triggered automatically after any new row is inserted in the ORDERS table. Similarly, we can have BEFORE TRIGGER, which triggers automatically before any modification is done on the desired table. TRIGGERs are also classified based on the statement triggered.
The TRIGGERS are basically used when it is not possible to use CHECK constraints due to performance issues or complex business logics. The TRIGGERS can be created using the “CREATE TRIGGER” command and they can be deleted using “DROP TRIGGER” command.
For example, if we want to create a TRIGGER we can give the following command.
CREATE TRIGGER ORDERDISCOUNT AFTER INSERT ON ORDERS FOR EACH ROW MODE DB2SQL BEGIN ATOMIC UPDATE ORDERS SET ORDER_DISCOUNT=(10*ORDER_TOTAL)/100;
The trigger can be deleted using below command.
DROP TRIGGER ORDERDISCOUNT