详解MySQL数据库之触发器

简介

触发器是MySQL数据库中的一种特殊的存储过程，是一种与表事件相关的代码块，可以在数据的插入、更新、删除等事件中被自动触发执行。触发器可以对数据的修改进行限制和控制，可以用来实现强制数据一致性、数据审核等功能。

创建触发器

使用CREATE TRIGGER关键字来创建一个触发器：

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON table_name
[FOR EACH ROW]
trigger_body

其中：

trigger_name为触发器名称，需要在创建的时候指定。
BEFORE与AFTER用来指定触发时机，BEFORE表示在事件执行前触发，AFTER表示在事件执行后触发。
INSERT、UPDATE和DELETE用来指定触发器关联的事件。
table_name是触发器关联的表名。
FOR EACH ROW表示对于每一行数据都会执行一次触发器。
trigger_body是触发器的代码块，可以包含多个SQL语句，用分号;隔开。

示例一：防止删除部门时还有员工

在此示例中，我们创建一个触发器，用来防止删除部门时仍有员工属于该部门。如果删除某个部门时，该部门下仍有员工，触发器会将删除操作取消。

CREATE TRIGGER before_delete_department
BEFORE DELETE ON department
FOR EACH ROW
BEGIN
    IF EXISTS (SELECT * FROM employee WHERE department_id = old.id) THEN
        SET message_text = "删除该部门时必须先删除部门下的员工";
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = message_text;
    END IF;
END;

上述代码中，我们创建了一个名为before_delete_department的触发器，在department表中删除某一行数据前触发。在触发器的代码块中，我们使用了一个IF语句，判断删除部门前是否存在该部门下的员工。如果存在员工，则设置一个警告信息，并使用SIGNAL语句抛出一个45000级别的异常。

示例二：记录用户行为

在此示例中，我们创建一个触发器，用来记录用户对某个表的插入、更新、删除操作，以便进行审计。

CREATE TRIGGER log_user_activity
AFTER INSERT OR UPDATE OR DELETE ON table_name
FOR EACH ROW
BEGIN
    IF INSERTING THEN SET activity = 'INSERT';
    ELSEIF UPDATING THEN SET activity = 'UPDATE';
    ELSEIF DELETING THEN SET activity = 'DELETE';
    END IF;

    INSERT INTO user_activity_log (user_id, activity, table_name, row_id) 
    VALUES (CURRENT_USER(), activity, 'table_name', old.id);
END;

上述代码中，我们首先定义了一个名为log_user_activity的触发器。在这个触发器的代码块中，我们使用了三个IF语句，根据当前触发的事件是插入、更新还是删除，设置相应的活动类型activity。

然后我们将用户的操作记录下来，以便审计。我们定义了一个user_activity_log表，用来记录用户的行为。在定义表时，我们需要为每个行为指定一个activity类型，在这里我们使用一个变量来保存。使用INSERT INTO语句将用户行为插入到user_activity_log表中，记录用户的ID、活动类型、表名和行ID。