Triggers in SQL

1. What is a trigger ?

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:

 

2. Can you have a COMMIT or ROLLBACK in a Trigger?

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;

3.What are the different types of Triggers?

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.

  • BEFORE trigger : This trigger is called before the execution of the DML statement. This BEFORE trigger can be used for some condition check or it can be used to alter the whole DML statement so that it cannot be executed on the table. For example, if the student age is less than 10, don’t allow to insert the record into the table.
  • After Trigger : this trigger is called after once DML statement is executed. It can perform any kind of transaction.
  • Combination of triggers : We can have combination of row, statement, BEFORE and AFTER triggers.
  • BEFORE STATEMENT : This trigger is executed only once before executing the DML statement.
  • BEFORE ROW : This trigger is executed for each row of the table, but before the DML execution.
  • AFTER STATEMENT : This trigger is executed only once after the DML execution is complete
  • AFTER ROW : This trigger is executed once the DML statement is complete, but for each row of the table.

4. What is the execution order of Triggers?

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.

  • BEFORE statement trigger is always called first. It is called before executing the whole DML statement and affecting the table.
  • BEFORE row level trigger is then called for each row.
  • Once the DML statement is executed, AFTER row level trigger is called for each row of the table.
  • At the end, AFTER statement level trigger is called.

Note: this is the order in which single trigger with different types will be executed.

5. What is Mutating table? What is the solution for it?

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 :

  • Avoid triggers : Triggers are not called by the users. They are implicitly called by DML statements. Hence we will not have control on triggers. In addition, there will be multiple triggers on the same table, which will contradict with one another leading to mutation. Hence better not to use them as much as possible.
  • Usage of AFTER or INSTEAD OF triggers will avoid mutation. If we are using AFTER trigger, it will be executed once the DML transaction is complete. Hence lock on the table would have been released. If we are using INSTEAD OF trigger, it will undo the DML transaction and then it will perform trigger transaction. Hence there will not be any mutation.
  • Try to avoid DML transaction on the same calling table in the triggers. This will not create any lock on the table and hence no mutation.

6. What is the maximum number of triggers that can be created on single table ?

We can have maximum of 12 triggers on a table. Care should be taken while using all these triggers.


< Prev
Scroll to Top