MySQL Trigger

 

A MySQL trigger is a stored program that is invoked automatically in response to an event such as INSERT, UPDATE, or DELETE to a particualar table.

There are two types of triggers

1. Row-level trigger: a row-level trigger is activated for each row that is inserted, updated, or deleted.
2. Statement-level trigger: a statement-level trigger is executed once for each transaction regardless of how many rows are inserted, updated, or deleted. MySQL supports only row-level triggers. It doesn’t support statement-level triggers.

Advantages of triggers

1. Triggers provide another way to check the integrity of data
2. Triggers handle errors from the database layer
3. Triggers are another way to run cron jobs.
4. Triggers can be useful for auditing the data changes in tables.

Disadvantages of triggers

1. Triggers can only use extended validations but not all validations. You can use NOT NULL, UNIQUE, CHECK, and FOREIGN KEY contraints.
2. Triggers can be difficult to troubleshoot because they execute automatically in the database which may not be visible to clients.
3. Triggers may increase the overhead of the MySQL server.

Create a Trigger

1. Specify the name of the trigger – CREATE TRIGGER trigger_name
2. Specify trigger type – AFTER INSERT
3. Specify the name of the table for which you want to create the trigger for – ON table_name
4. Specify the trigger body that will run every the trigger is invoked.

DELIMITER $$
 
CREATE TRIGGER trigger_name
    AFTER INSERT
    ON table_name FOR EACH ROW
BEGIN
   -- statements
   -- variable declarations

   -- trigger code
END$$    
 
DELIMITER ;

Example

DELIMITER $$
 
CREATE TRIGGER after_members_insert
AFTER INSERT
ON members FOR EACH ROW
BEGIN
    IF NEW.birthDate IS NULL THEN
        INSERT INTO reminders(memberId, message)
        VALUES(new.id,CONCAT('Hi ', NEW.name, ', please update your date of birth.'));
    END IF;
END$$
 
DELIMITER ;

Triggers only run for the new, updated, or deleted rows. They don’t touch those rows that didn’t get updated.

Show triggers

SHOW TRIGGERS;

SHOW TRIGGERS
[{FROM | IN} database_name]
[LIKE 'pattern' | WHERE search_condition];

SHOW TRIGGERS; -- return all triggers in all databases

SHOW TRIGGERS 
FROM database_name; -- show triggers from database_name

The only way to edit a trigger is by dropping and recreating it.

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name;

 

---