Triggers in MySQL are specialized stored procedures that automatically execute in response to predefined events, such as INSERT, UPDATE, or DELETE operations on a table. Unlike stored procedures, which require explicit calls, triggers are invoked automatically when their associated events occur.

MySQL supports three types of triggers: INSERT, UPDATE, and DELETE triggers.

1. INSERT Triggers

INSERT triggers respond before or after an INSERT statement is executed. Key points to note:

• Within an INSERT trigger, you can reference a virtual table named NEW (case-insensitive) to access the row being inserted.
• In a BEFORE INSERT trigger, values in NEW can be modified, allowing changes to the inserted data (subject to appropriate permissions).
• For AUTO_INCREMENT columns, NEW contains 0 before the INSERT and the newly generated value after the INSERT.

2. UPDATE Triggers

UPDATE triggers respond before or after an UPDATE statement is executed. Key points to note:

• Within an UPDATE trigger, reference NEW to access the updated values and OLD (case-insensitive) to access values before the update.
• In a BEFORE UPDATE trigger, values in NEW can be modified to alter the data used in the UPDATE statement.
• Values in OLD are read-only and cannot be changed.
• Important: When a trigger updates the same table that triggered it, only BEFORE triggers are allowed; AFTER triggers are not permitted.

3. DELETE Triggers

DELETE triggers respond before or after a DELETE statement is executed. Key points to note:

• Within a DELETE trigger, reference OLD to access the row being deleted.
• Values in OLD are read-only and cannot be updated.

Creating Triggers

Syntax:

CREATE TRIGGER <trigger_name>
<BEFORE | AFTER>
<INSERT | UPDATE | DELETE>
ON <table_name>
FOR EACH ROW
<trigger_body>

Explanation:

1. Trigger Name: Must be unique within the database. Optionally prefix with the database name for specificity.
2. Trigger Event: Specifies the type of statement that activates the trigger (INSERT, UPDATE, or DELETE).
   • INSERT: Activated when new rows are inserted, including via LOAD DATA.
   • DELETE: Activated when rows are deleted, including via REPLACE.
   • UPDATE: Activated when rows are updated.
3. BEFORE | AFTER: Defines when the trigger fires relative to the triggering event. Use BEFORE for data validation and AFTER for post-event actions.
4. Table Name: The permanent table associated with the trigger. Temporary tables and views cannot have triggers. A table cannot have two triggers with the same event and timing.
5. Trigger Body: The SQL statements to execute when the trigger is activated. Use BEGIN…END for multiple statements.
6. FOR EACH ROW: Indicates row-level triggering; the trigger executes for each affected row. Each table supports up to six triggers (BEFORE and AFTER for INSERT, UPDATE, DELETE), with one trigger per event per timing.

Example: Creating Triggers

Example 1: Summming Scores on Insert This trigger calculates the total score from inserted rows in a student table.

-- Create trigger to sum scores
CREATE TRIGGER calculate_total_score
BEFORE INSERT ON student
FOR EACH ROW
SET @total_score = @total_score + NEW.score;

-- Activate trigger
SET @total_score = 0;
INSERT INTO student (username, password, city, area, score)
VALUES ('0001', '123456', 'Beijing', 'Beijing', 100),
       ('0002', '654321', 'Chongqing', 'Chongqing', 99);
SELECT @total_score;

Example 2: Validating and Updating on Order Insertion These triggers manage order insertion by validating user existence and updating order counts.

-- Trigger 1: Validate user before inserting order
DROP TRIGGER IF EXISTS validate_user_before_order;
CREATE TRIGGER validate_user_before_order
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
    DECLARE user_exists INT DEFAULT 0;
    SELECT id INTO user_exists FROM user WHERE id = NEW.user_id;
    IF user_exists <= 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'User ID does not exist.';
    END IF;
END;

-- Attempt to insert an order with invalid user
INSERT INTO orders (user_id, title, price) VALUES (200, 'aaa', 333);

-- Trigger 2: Update user order count after inserting order
DROP TRIGGER IF EXISTS update_order_count;
CREATE TRIGGER update_order_count
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    UPDATE user SET order_count = IFNULL(order_count, 0) + 1 WHERE id = NEW.user_id;
END;

-- Insert a valid order
INSERT INTO orders (user_id, title, price) VALUES (2, 'aaaaa', 333);

Viewing Triggers

To inspect triggers in your database:

-- List all triggers in the test database
SHOW TRIGGERS FROM test;

-- List triggers for a specific table
SHOW TRIGGERS LIKE 'orders';

-- View a specific trigger
SHOW TRIGGERS LIKE 'validate_user_before_order';
SHOW TRIGGERS WHERE `Table` = 'orders' AND `Trigger` = 'validate_user_before_order';

-- Display the creation statement for a trigger
SHOW CREATE TRIGGER validate_user_before_order;

Deleting Triggers

To remove a trigger:

DROP TRIGGER IF EXISTS calculate_total_score;

Table Structures for Testing

Student Table:

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `age` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;

Orders Table:

CREATE TABLE `orders` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `title` varchar(128) DEFAULT NULL COMMENT 'Product Name',
  `price` int(11) DEFAULT NULL COMMENT 'Price (cents)',
  `create_date` datetime DEFAULT NULL,
  `update_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8mb4;