- Trending Categories
- Data Structure
- Operating System
- C Programming
- Social Studies
- Fashion Studies
- Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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.
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.
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
- Write the syntax to declare a scrollable cursor on the ORDERS DB2 table.
- What happens with the trigger when we will drop the table having that trigger?
- How will you find the ORDER_ID of all the orders having ORDER_TOTAL greater than the average of ORDER_TOTAL in ORDER's DB2 table
- What is STORED PROCEDURE in a DB2? How will you create a new stored procedure?
- How can we create and use a MySQL trigger?
- Write the DB2 SQL query to find the third highest ORDER_TOTAL in a ORDERS DB2 table
- Write a DB2 query to find out all the duplicate INVOICE_ID in ORDERS DB2 table?
- How can we destroy a trigger?
- MySQL trigger to insert row into another table?
- Implement MySQL trigger in the first table to insert records in the second table?
- Check if Table, View, Trigger, etc present in Oracle
- How can I clone/duplicate the table along with its data, trigger and indexes?
- How will you find out all the indexes which are built in a particular DB2 table?