SQL | Triggers

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.

Example –
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:

  1. AFTER INSERT activated after data is inserted into the table.
  2. AFTER UPDATE: activated after data in the table is modified.
  3. AFTER DELETE: activated after data is deleted/removed from the table.
  4. BEFORE INSERT: activated before data is inserted into the table.
  5. BEFORE UPDATE: activated before data in the table is modified.
  6. 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.



My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.




Article Tags :
Practice Tags :


1


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.