MySQL Trigger

In MySQL, trigger can also be created. There are 6 type of triggers that can be made they are:-

After/Before insert
After/Before update
After/Before delete

1. AFTER/BEFORE INSERT Trigger

In MySQL, AFTER/BEFORE trigger can also be created. AFTER/BEFORE trigger means trigger will invoke after the record is inserted.

Syntax

snippet

CREATE TRIGGER trigger_name
	AFTER/BEFORE INSERT
	     ON table_name FOR EACH ROW
	     BEGIN
		--variable declarations
		--trigger code
	    END;

Parameter:

trigger_name: name of the trigger to be created.

AFTER/BEFORE INSERT: It points the trigger after or before insert query is executed.

table_name: name of the table in which a trigger is created.

Example

Step 1: Create a table, for example, student and insert the records.

Step 2: Create another table, for example, student1 and don't insert the records.

Step 3: Now create AFTER INSERT trigger

Query;

CREATE TRIGGER 'student_insert' AFTER INSERT ON 'student' FOR EACH ROW INSERT INTO student2 values(null, new.id, 'inserted', NOW());

Step 4: After creating trigger, now again insert the record in student table it will reflect on student2 table.

i) Inserted in student table id 6

ii) Reflected in student2.

2. AFTER/ BEFORE UPDATE Trigger

In MySQL, AFTER/BEFORE UPDATE trigger can also be created. AFTER/BEFORE UPDATE trigger means trigger will invoke after/before the record is updated.

Syntax

snippet

CREATE TRIGGER trigger_name
	AFTER/BEFORE UPDATE
	     ON table_name FOR EACH ROW
	     BEGIN
		--variable declarations
		--trigger code
	    END;

Parameter:

trigger_name: name of the trigger to be created.

AFTER UPDATE: It points the trigger update query is executed.

table_name: name of the table in which a trigger is created.

Example

Step 1: Create a table, for example, student and insert the records.

Step 2: Create another table, for example, student1 and insert the records.

Step 3: Now create AFTER UPDATE trigger

Query:

CREATE TRIGGER 'student_update' AFTER UPDATE ON 'student' FOR EACH ROW UPDATE student2 SET name=new.name where id=id;

Step 4: After creating trigger now again update the record in student table it will reflect on student2 table.

i) update in student table id 6

ii) Reflected in student2.

3. AFTER/BEFORE DELETE Trigger

In MySQL, AFTER/BEFORE DELETE trigger can also be created. AFTER/BEFORE DELETE trigger means trigger will invoke after/before the record is deleted.

Syntax

snippet

CREATE TRIGGER trigger_name
	AFTER/BEFORE DELETE
	     ON table_name FOR EACH ROW
	     BEGIN
		--variable declarations
		--trigger code
	    END;

Parameter:

trigger_name: name of the trigger to be created.

AFTER/BEFORE DELETE: It points the trigger after/before delete query is executed.

table_name: name of the table in which a trigger is created.

Example

Step 1: Create a table, for example, student and insert the records.

Step 2: Create another table, for example, student1 and insert the records.

Step 3: Now create AFTER DELETE trigger

Query:

CREATE TRIGGER 'student_delete' AFTER DELETE ON 'student' FOR EACH ROW DELETE FROM student2 WHERE student.id=student1.id;

Step 4: After creating a trigger, now again delete the record in student table it will reflect on student2 table.

i) delete in student table id 6

ii) Reflected in student2.

DROP TRIGGER

In MySQL Trigger can also be drop. When Trigger drops, then it is removed from the database.

Syntax

snippet

Drop Trigger[ IF EXISTS ] Trigger_name;

Parameter:

Trigger_name: Name of the Trigger to be dropped

Example 1

snippet

drop Trigger student_update;