MySQL Trigger

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

  1. After/Before insert
  2. After/Before update
  3. 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.

MySQL Trigger

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

MySQL Trigger

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());

MySQL Trigger

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

MySQL Trigger

ii) Reflected in student2.

MySQL Trigger

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.

MySQL Trigger

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

MySQL Trigger

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;

MySQL Trigger

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

MySQL Trigger

ii) Reflected in student2.

MySQL Trigger

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.

MySQL Trigger

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

MySQL Trigger

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;

MySQL Trigger

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

MySQL Trigger

ii) Reflected in student2.

MySQL Trigger

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;
MySQL Trigger
Related Tutorial
Follow Us
https://www.facebook.com/Rookie-Nerd-638990322793530 https://twitter.com/RookieNerdTutor https://plus.google.com/b/117136517396468545840 #
Contents