Types of triggers

Timing

There are two trigger action time modifiers :

BEFORE trigger activates before executing the request,
AFTER trigger fire after change.

Triggering event

There are three events that triggers can be attached to:

INSERT
UPDATE
DELETE

Before Insert trigger example

DELIMITER $$

CREATE TRIGGER insert_date 
    BEFORE INSERT ON stack
    FOR EACH ROW
BEGIN
        -- set the insert_date field in the request before the insert
    SET NEW.insert_date = NOW();
END;

$$
DELIMITER ;

Before Update trigger example

DELIMITER $$

CREATE TRIGGER update_date 
    BEFORE UPDATE ON stack
    FOR EACH ROW
BEGIN
        -- set the update_date field in the request before the update
    SET NEW.update_date = NOW();
END;

$$
DELIMITER ;

After Delete trigger example

DELIMITER $$

CREATE TRIGGER deletion_date 
    AFTER DELETE ON stack
    FOR EACH ROW
BEGIN
        -- add a log entry after a successful delete
    INSERT INTO log_action(stack_id, deleted_date) VALUES(OLD.id, NOW());
END;

$$
DELIMITER ;

Found a mistake? Have a question or improvement idea? Let me know.

Table Of Contents