What Is MySQL Triggers?

The MySQL trigger is a database object that is associated with a table. It will be activated when a defined action is executed for the table. The trigger can be executed when you run one of the following MySQL statements on the table: INSERT, UPDATE and DELETE and it can be invoked before or after the event.

Definition –
A trigger is a named database object that is associated with a table, and that activates when a particular event occurs for the table. Some uses for triggers are to perform checks of values to be inserted into a table or to perform calculations on values involved in an update.
A trigger is defined to activate when a statement inserts, updates, or deletes rows in the associated table. These row operations are trigger events. For example, rows can be inserted by INSERT or LOAD DATA statements, and an insert trigger activates for each inserted row. A trigger can be set to activate either before or after the trigger event. For example, you can have a trigger activate before each row that is inserted into a table or after each row that is updated.

Uses for Triggers

• Complex Auditing
• Enforce Business Rules
• Derive Column Values
• Triggers Are Tricky!
• automatically generate derived column values
• prevent invalid transactions
• enforce complex security authorizations
• enforce referential integrity across nodes in a distributed database
• enforce complex business rules
• provide transparent event logging
• provide sophisticated auditing
  Types of Triggers
  • DML Triggers
  • DDL Triggers

Logon Triggers

DML Triggers

DML stands for Data Manipulation Language. INSERT, UPDATE, and DELETE statements are DML statements. DML triggers get fired whenever data is modified using INSERT, UPDATE, and DELETE events.

DML triggers can be again classified into 2 types.
After triggers (Sometimes called FOR triggers)

1. Instead of triggers
2. After triggers

After triggers get fired after only with a condition when a modification action occurs. The INSERT, UPDATE and DELETE commands because of an after trigger gets fired after the execution of a complete statement.

Create Trigger Syntax:

CREATE TRIGGER trigger_name trigger_time trigger_event

ON table_name

FOR EACH ROW

BEGIN

...

END;

Sample example of create and call trigger
Try It Now

Drop trigger using-

DROP TRIGGER IF EXISTS <trigger_name>;

Show trigger definition-

SHOW CREATE TRIGGER <trigger_name>; 
Ex : SHOW CREATE TRIGGER after_insert_trigger; 
Try It Now

Show list of Triggers-

SHOW TRIGGERS; 

Try It Now