_edited_edited.png)
A Trigger is a statement which executes automatically when there is a modification (insert, update, delete and, truncate) to the database. A trigger contains two parts, first when (before or after modification) the trigger is to be executed and second, the action to be performed when the trigger executes. Based on the above two criteria, the triggers are classified into six categories which are listed below.
BEFORE INSERT -Trigger activated before data is inserted into the table.
AFTER INSERT -Trigger activated after data is inserted into the table. BEFORE DELETE -Trigger gets activated before data is deleted from the table. AFTER DELETE -Trigger gets activated after data is deleted from the table. BEFORE UPDATE -Trigger activated before data is modified in the table. AFTER UPDATE -Trigger activated after the data is modified in the table.
Just like the tables, triggers can also be created, replaced, altered and deleted. A trigger in PostgreSQL is created in two parts as explained below:
CREATE TRIGGER FUNCTION : To create a trigger, first of all, a trigger function is created which executes once the related trigger is invoked. Unlike a regular user-defined function, a trigger function does not take any argument and return value with the type trigger. The syntax for creating a trigger function is
CREATE TRIGGER : After creating a trigger function, a trigger is framed on the table, which includes the declaration that when the trigger is supposed to be invoked means before or after modification to the table. The body of trigger also includes the type of trigger which can be:
Row-level trigger
Statement-level trigger
A row-level trigger is fired for each row modification while a statement-level trigger is fired for each transaction.
The syntax for creating a trigger is
Here are few examples presented for creating triggers on the events INSERT, UPDATE and DELETE on a table. For this, a table named ‘employee’ with columns ‘emp_id’, ‘emp_name’, ‘age’, ‘address’, ‘salary’ ,‘ last_updated’ has been created and 12 records have been added in it.
After that 3 more tables have been created as below.
The table ‘updated_address’ has been created to have a back up of old address as well as new address of an employee. The table ‘ex_employee’ has been created to maintain a backup of the employees who are no more the employee of the company, whereas the table ‘count_emp’ has been created to count the records of the employees after adding new employee or removing ex-employee from the table ‘employee’. Let’s create a update trigger.
UPDATE TRIGGER: A update trigger has been created which is fired after the address of an employee is updated in ‘employee’ table and in result of the invoked trigger, also the table ‘updated_address’ has been updated which contains id, old address and new address of the employee.
NEW and OLD are special variables that you can use with PL/SQL triggers without explicitly defining them in row-level triggers. NEW refers to the new table row for insert and update operations, whereas OLD refers to the old table row for update and delete operations.
After creating trigger, let’s us update the address of two employees in table ‘employee’ .
And the output is
Let’s create triggers on the INSERT and DELETE event.
INSERT and DELETE TRIGGER: Here two triggers are created one of which is on DELETE event and other is on both INSERT and DELETE events.
In the below trigger, on each deletion in the ‘employee’ table, the record of the employee has been added to table ‘ex_employee’.
In the next trigger, the count of records of the ‘employee’ table has been updated after each insertion and deletion of the record in the table ‘employee’.
After creating the above two triggers, now it is time to execute these two triggers. For this first of all, let’s add two records in the table ‘employee’ and then check the record in the table ‘count_emp’.
The value of column num_emp in the table ‘count_emp’ has been updated to 14 which was 12 previously.
Now, to execute DELETE command on the table employee, let’s delete few records from the table ‘employee’.
And while deleting the records, two triggers ex_employee and count_emp get executed which results into the update in the tables ‘ex_employee’ and ‘count_emp’ and the output is shown as.
While creating a trigger on more than one events, TG_when should be same for all the events. For example, one cannot club BEFORE INSERT and AFTER DELETE. It should be either BEFORE INSERT OR DELETE or AFTER INSERT OR DELETE.