Trigger is a stored procedure which executes when special event occurs on database. In Triggers we write code to execute in response with the INSERT, UPDATE and DELETE statements. We can create Triggers on table, view and database.

CREATE TRIGGER trigger_name
[BEFORE | AFTER]
[INSERT | UPDATE | DELETE]
ON table | view | database
FOR each row | FOR each column
trigger_body; 

• CREATE TRIGGER trigger_name – This represents the Trigger is created with user defined name. Trigger name should be UNIQUE.
• BEFORE | AFTER – This represents when the trigger is going to execute.
• INSERT | UPDATE | DELETE - This represents on which statement is executes on table.
• ON table | view | database - This represents on which we want to create trigger.
• FOR each row | FOR each column - This represents the trigger executes either for each row changes or for each column changes.

• Triggers provides security on tables which are in database.
• Triggers provide DATA integrity.
• By using Triggers, we can find errors.
• We can call stored procedures or functions within the triggers.
• By using Triggers, we can validate data on INSERT and DELETE statement by batch instead of row by row.
• Triggers prevent Invalid transactions.
• Triggers are especially useful for auditing changes in the table.
• Triggers give an alternative way to run schedule task.

• Triggers cannot provide all validations. It provides extended validations only.
• Triggers are invisible so it is difficult to find what happens in database.
• Triggers enforce high load on database.
• Triggers are not suggested for high velocity of data, because triggers triggered all the time

Let us consider we have employee_details table with column emp_id, emp_name, manager_id, designation, date_of_hire, salary, det_id as follows –

Scenario – Creating Trigger on table with BEFORE INSERT statement.

Requirement – Creating emp trigger on employee_details BEFORE INSERT statement to double the salary. The query was as follows –

CREATE TRIGGER emp
BEFORE INSERT 
ON employee_details 
FOR each row
SET employee_details.salary = salary * 2 ;  

By executing above query we can create emp trigger on employee_details table. When ever we insert values in the table, Before that trigger executed first and the salary multiplied by 2 and result stored in salary column as shown in below -

INSERT INTO employee_details(emp_id,emp_name,designation,date_of_hire,
salary,dept_id) 
VALUES(001,'Employee1','Director','2019-07-11',45000.00,1000);

By executing above query we can get output like as shown in below –

Scenario – Creating Trigger on table with AFTER INSERT statement.

Requirement – Creating emp trigger on employee_details AFTER INSERT statement to INSERT values in employee_info table. The query was as follows –

CREATE TABLE employee_info ( 
               emp_id INT, 
               emp_name VARCHAR (20),
               salary INT);         

Inserting rows in employee_info from employee_details table by using trigger with AFTER INSERT as follows –

CREATE TRIGGER employee
AFTER INSERT
ON employee_details
FOR each row
INSERT INTO employee_info VALUES(employee_details.emp_id,
employee_details.emp_name, employee_details.salary); 

By executing above query we can insert rows in employee_info from employee_details table. The output was as follows –