Trigger is a statement that a system executes automatically when there is any modification to the database. In a trigger, we first specify when the trigger is to be executed and then the action to be performed when the trigger executes. Triggers are used to specify certain integrity constraints and referential constraints that cannot be specified using the constraint mechanism of SQL.
Suppose, we are adding a tupple to the ‘Donors’ table that is some person has donated blood. So, we can design a trigger that will automatically add the value of donated blood to the ‘Blood_record’ table.
Types of Triggers –
We can define 6 types of triggers for each table:
- AFTER INSERT activated after data is inserted into the table.
- AFTER UPDATE: activated after data in the table is modified.
- AFTER DELETE: activated after data is deleted/removed from the table.
- BEFORE INSERT: activated before data is inserted into the table.
- BEFORE UPDATE: activated before data in the table is modified.
- BEFORE DELETE: activated before data is deleted/removed from the table.
Examples showing implementation of Triggers:
1. Write a trigger to ensure that no employee of age less than 25 can be inserted in the database.
delimiter $$ CREATE TRIGGER Check_age BEFORE INSERT ON employee FOR EACH ROW BEGIN IF NEW.age < 25 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'ERROR: AGE MUST BE ATLEAST 25 YEARS!'; END IF; END; $$ delimiter;
Explanation: Whenever we want to insert any tupple to table ’employee’, then before inserting this tupple to the table, trigger named ‘Check_age’ will be executed. This trigger will check the age attribute. If it is greater then 25 then this tupple will be inserted into the tupple otherwise an error message will be printed stating “ERROR: AGE MUST BE ATLEAST 25 YEARS!”
2. Create a trigger which will work before deletion in employee table and create a duplicate copy of the record in another table employee_backup.
Before writing trigger, we need to create table employee_backup
create table employee_backup (employee_no int, employee_name varchar(40), job varchar(40), hiredate date, salary int, primary key(employee_no));
delimiter $$ CREATE TRIGGER Backup BEFORE DELETE ON employee FOR EACH ROW BEGIN INSERT INTO employee_backup VALUES (OLD.employee_no, OLD.name, OLD.job, OLD.hiredate, OLD.salary); END; $$ delimiter;
Explanation: We want to create a backup table that holds the value of those employees who are no more the employee of the institution. So, we create a trigger named Backup that will be executed before the deletion of any Tupple from the table employee. Before deletion, the values of all the attributes of the table employee will be stored in the table employee_backup.
3. Write a trigger to count number of new tupples inserted using each insert statement.
Declare count int Set count=0; delimiter $$ CREATE TRIGGER Count_tupples AFTER INSERT ON employee FOR EACH ROW BEGIN SET count = count + 1; END; $$ delimiter;
Explanation: We want to keep track of the number of new Tupples in the employee table. For that, we first create a variable ‘count’ and initialize it to 0. After that, we create a trigger named Count_tupples that will increment the value of count after insertion of any new Tupple in the table employee.
Attention reader! Don’t stop learning now. Get hold of all the important CS Theory concepts for SDE interviews with the CS Theory Course at a student-friendly price and become industry ready.