A trigger is a set of code which is executed in response to some event.
E.g Update employee_perfomance table when a new task is inserted in task table. Here, the trigger is “update” and the event is “inserting a new row”.

A trigger in MySQL is created using CREATE TRIGGER trigger_name. We need to specify the trigger type.

Triggers can be used for actively control, monitor, and manage a group of tables whenever an insert, update, or delete operation is performed.

Write syntax for Creating triggers and Dropping Triggers along with example for each of them.

Create trigger

Syntax:
Create trigger trigger_name
ON table_name
{
FOR [delete][update][insert]
AS
Sql statements
}

Example:

CREATE TRIGGER reminder
ON titles
FOR INSERT, UPDATE
AS some_function (50009, 16, 10)

Drop a trigger

Syntax:
DROP TRIGGER Trigger_name

Example:
Drop trigger reminder

What are the types of triggers? Explain each of them. – Insert triggers, Delete triggers and Update triggers.

Types of triggers:

Insert trigger: this trigger is invoked after or before an insert on some table.

Example:

CREATE TRIGGER new_employee
After INSERT ON employee
FOR EACH ROW
Sql statement

Update trigger: this trigger is invoked after or before an update on some table.

Example:

CREATE TRIGGER new_employee
After UPDATE ON employee
FOR EACH ROW
Sql statement

Delete trigger: this trigger is invoked after or before an delete on some table.

Example:

CREATE TRIGGER new_employee
After DELETE ON employee
FOR EACH ROW
Sql statement

MySQL Triggers - Jan 23, 2009 by Rajmeet Ghai

How are triggers created in MySQL?

A trigger is a set of code which is executed in response to some event.

E.g Update employee_perfomance table when a new task is inserted in task table. Here, the trigger is “update” and the event is “inserting a new row”.

A trigger in MySQL is created using CREATE TRIGGER trigger_name. We need to specify the trigger type.

Create trigger

Syntax:
Create trigger trigger_name
ON table_name
{
FOR [delete][update][insert]
AS
Sql statements
}

Example:
CREATE TRIGGER reminder
ON titles
FOR INSERT, UPDATE
AS some_function (50009, 16, 10)

What are the levels of triggers supported by MySQL?

A trigger is a set of code which is executed in response to some event.

E.g Update employee_perfomance table when a new task is inserted in task table. Here, the trigger is “update” and the event is “inserting a new row”.

A trigger in MySQL is created using CREATE TRIGGER trigger_name. we need to specify the trigger type.

1. When do you want the trigger to execute? This can be either BEFORE or AFTER

2. What do you expect the trigger to do? This can be INSERT UPDATE DELETE

3. On which table you want the trigger to run? (using ON table_name)

4. Lastly, though not mandatory, is FOR EACH ROW if it is used then the trigger will fire once for all records of the table. If it is not specified the trigger will fire once only regardless of the number of records being updated

How can we obtain Metadata about triggers in MySQL?

Using the show triggers statement. Show trigger lists the triggers currently defined for tables in a database.
Example
SHOW TRIGGERS LIKE ‘emp%’\G
The Triggers table can be queried. The triggers table provides the information about the triggers. The table has a variety of INFORMATION SCHEMA that can be queried.