Javatpoint Logo
Javatpoint Logo

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

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

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

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

Parameter:

Trigger_name: Name of the Trigger to be dropped

Example 1

MySQL Trigger




Please Share

facebook twitter google plus pinterest

Learn Latest Tutorials


Preparation


Trending Technologies


B.Tech / MCA