Triggers are blocks of PL/SQL code, which are executed as a result of any insert / update / delete on the table. There will not be any explicit call for triggers. It is always automatically executed. It might perform single query or set of queries to meet the goal. But it will be always written as a PL/SQL block. It is always associated with insert / update / delete of a table. But the set of transaction inside the trigger can be performed on the same table or one or more other tables.
The general syntax of a trigger is as follows:
No. This is because of the following reason: Triggers are called when there is any DML statement is executed. That means, any DML statement is a part of one transaction. This transaction has various steps like execute the DML statement; as a result of it execute the triggers. This trigger may be called at each row level. In other words, for each row insertion/deletion/updation, trigger will be called. This transaction will be complete only when all the row level DML actions are executed and its corresponding triggers are executed. But if we issue a COMMIT or ROLLBACK within the trigger, it is complete or revert the whole transaction, keeping rest of the DML statement un-executed. This is not expected as there will be inconsistency or redundancy in the table. Therefore, any COMMIT or ROLLBACK is not allowed within row level triggers.
We can use COMMIT or ROLLBACK within the trigger with the help of AUTONOMOUS_TRANSACTION. But one need to be very careful while using this. When AUTONOMOUS_TRANSACTION is used within the trigger, it will consider trigger as a separate transaction and it commit/rollback only those actions within the trigger. It will not commit or rollback the actions outside the trigger or the one which triggered the trigger. In other words AUTONOMOUS_TRANSACTION will make trigger independent from the triggering transaction.
CREATE OR REPLACE TRIGGER tr_Example AFTER UPDATE OF COL3 ON TABLE_EXAMPLE FOR EACH ROW DECLARE PRAGMA AUTONOMOUS_TRANSACTION; BEGIN INSERT INTO AUDIT_EXAMPLE (COL1, COL2, NEW_COL3, OLD_COL3, COL4) VALUES (: old.col1,: old.col2,:new.col3, : old.col3, SYSDATE); COMMIT; END;
There are two types of triggers.
Row level trigger: - Row level trigger is executed when each row of the table is inserted/ updated/ deleted. If it is a row level trigger, then we have to explicitly specify while creating the trigger, as we did in the above example. Also, we have to specify the WHEN (condition) in the trigger.
Statement level trigger: - this trigger will be executed only once for DML statement. This DML statement may insert / delete/ update one row or multiple rows or whole table. Irrespective of number of rows, this trigger will be fired for the statement. If we have not specified the type of trigger while creating, by default it would be a statement level trigger.
In below example, once the EMPLOYEES are inserted, it calculates their age and automatically updates EMPLOYEES table.
CREATE OR REPLACETRIGGER tr_calc_age AFTER INSERT ON EMPLOYEES BEGIN UPDATE EMPLOYEES emp SET AGE = SYSDATE – emp.DATE_OF_BIRTH WHERE emp.EMPLOYEE_ID =:old.EMPLOYEE_ID; EXCEPTION When OTHERS THEN raise_application_error (-20015, ‘Error while updating in tr_calc_age_trigger); END;
In both the cases, the DML statement may be on particular column(s) or on whole table.
In addition to above types of trigger, we can have triggers which are called so because of the time when they are executed.
We can have multiple types triggers on the same table. Since there is BEFORE, AFTER, INSTEAD OF, ROW level, STATEMENT level etc, there should be some order for it to execute. The order of execution of trigger is as follows.
Note: this is the order in which single trigger with different types will be executed.
Suppose we have inserted some records into STUDENT table. We have created a row level trigger on the same table to compute the total number of records after the insertion. What is wrong in this situation? It looks perfect right? But it will throw a mutating table error. What happens in this case is, STUDENT table is inserted with records. The row level trigger will be fired after each row insertion. But transaction on STUDENT is still not complete and trigger is trying to access STUDENT. There will be INSERT lock on STUDENT which is not released for trigger to select the count. Hence trigger will be waiting for the insert to complete. But the insert is waiting for the trigger to complete to proceed for the next row insertion or to complete the transaction. Hence both will be waiting for each other to complete their transaction. Such a condition on the table is called mutating error.
Similarly, if there are two tables A and B, and two triggers where one of the trigger will be updating the table B when there is insertion on A and another trigger will be inserting on the table A when there is update on B. In this case both the tables will be waiting for each other. This is also another condition of mutating tables. It is a kind of deadlock situation when triggers are used.
Solution for mutating tables :
We can have maximum of 12 triggers on a table. Care should be taken while using all these triggers.