How will you create a new TRIGGER on the ORDERS DB2 table? Give the syntax of TRIGGER

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.

Example

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 INSERT trigger gets executed when an INSERT query inserts data in the DB2 database.
• The UPDATE trigger activates when an UPDATE query modifies data in the DB2 database.
• The DELETE trigger gets executed when the DELETE query removes the data from DB2 database.

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.

Example

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