What are triggers? What are they used for?
- 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.
1. Create triggerSyntax: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)
2. Drop a triggerSyntax: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:1. 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
2. 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
3. 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
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_performance 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 triggerSyntax: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?
1. Using the show triggers statement. Show trigger lists the triggers currently defined for tables in a database.
ExampleSHOW TRIGGERS LIKE ‘emp%’\G
2. 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.
