Triggers in DBMS - TutorialCup

Table of Contents

Introduction

Suppose we are updating the marks in the STUDENT database for some of the students after re-evaluation. When we are updating the marks, their old values are lost. But in general view it is better to keep the old marks too – just to know what was the mark before and what is now. But we do not have any columns to store old marks in the MARKS table. We have to have a separate table to store the update log for marks updates. But this cannot be done in single MARKS table update. In this case, we need to update MARKS table as well as we need to insert an entry in the MARKS_LOG table. Both are different transactions.

What can be done in this case? We can write a procedure to update and insert and call it every time we have to update marks. But main purpose of the person who is updating the mark is only to update the marks. But inserting into log table is secondary for him or least important for him, but at the same it is an important to store that information somewhere. So he will use only UPDATE statement to correct the marks. In such case triggers are used to insert the record into log files.

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:

A typical trigger has 3 main components

Triggering SQL Statement: – This is the DML statement which causes the triggers to be invoked. That is it tells when to call the trigger – before or after, on which DML statement of the table – INSERT/ UPDATE/ DELETE and whether to call trigger when whole table is processed or only few columns are processed. BEFORE and AFTER is used on tables and INSTEAD OF is used on views to create triggers. For example, it tells when to call the trigger to insert the logs – whether to call it on Inserting, deleting or updating MARKS table, before or after updating the MARKS table, whether to call the trigger on updating all columns of MARKS table or on particular columns of MARKS table.
Trigger Restriction: – this is the part of trigger which tells how many times the trigger needs to be executed. It informs, if the trigger has to be called for each row insert /update/delete, or only once for the transaction. In our example, it would be for each row, because, we need to have log for each row of student marks update. There would be some cases where if we perform some DML statement on table, say first and last entry of days’ transaction in the supermarket billing to be logged in some other table. In this case, each INSERT will not call the trigger, instead it will call only twice in a day to log the entry.
Trigger Action: – this part will actually perform set of transaction as result of original DML statement. For example, inserting the records into log tables. It need not be inserting logs always. It can be any transaction. For example, when date of birth of a student is inserted, trigger might calculate his age and insert into the same table.

CREATE OR REPLACETRIGGER tr_marks_log	-- creates a trigger, if exists replaces it	
	AFTER	UPDATE OF m.sub_mark ON MARKS – after updating MARKS table’s sub_mark column
	FOR EACH ROW -- for each row update
BEGIN
	INSERT INTO marks_log (STD_ID, SUBJECT, OLD_MARK, NEW_MARK)
	 VALUES (:old.std_id,:old. subject, :old.sub_mark, :new.sub_mark);
EXCEPTION
	When OTHERS THEN
	       raise_application_error (-20015, ‘Error while inserting marks log’);
END;

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 Students are inserted, it calculates the age of the student and updates STUDENT table.

CREATE OR REPLACETRIGGER tr_calc_age	
	AFTER	INSERT ON STUDENT
BEGIN
	UPDATE STUDENT s 
	SET AGE = SYSDATE – s.DATE_OF_BIRTH 
	WHERE s.STD_ID =:old.STD_ID;
EXCEPTION
	When OTHERS THEN
	       raise_application_error (-20015, ‘Error while inserting marks log’);
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.

Some of the key points about trigger

Triggers do not have commit or rollback or savepoint in them. All the commit and rollback are mentioned in the main DML statement. If DML transaction is committed or rolled back, the transaction in trigger is also committed or rolled back. This is because, if we give commit or rollback in the trigger, it commits or rollbacks the DML statements too. But this is not desirable, as the triggers are meant to perform the transaction as a result of DML statements. Its purpose is not to confirm the DML transaction, but to perform additional transaction as a part of DML statement.
There is no :OLD values in INSERT triggers since it is totally a new record.
There is no :NEW values in DELETE triggers, as we are deleting the record.
If there are multiple triggers defined on the table, there are no specific criteria to execute them. All of them can perform simultaneously. Hence we might have to have another trigger to execute all these triggers in the order.
A trigger cannot change the table from which it has been called. If there is a trigger called as a result of insert on a table, then the trigger cannot insert/update the same table. This is because we have a lock on the table already and it will create dead lock situation.

Execution Order

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.

Mutating tables

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.