chapter12_数据库编程_5_触发器

作者: 米都都 | 来源:发表于2019-01-04 17:30 被阅读0次

(1) 触发器的作用是实现数据完整性，它比主键、外键、NOT NULL、UNIQUE更加灵活

(2) 触发器是特殊的存储过程。它与存储过程经历的过程类似（分析、解析、优化），但是没有接口，不能被显示调用，只能自动执行。

(3) 触发器是引发它的事务的一部分。只有触发器被正确执行，该事务才是完整的。

(4) 使用原则

1° 能用约束实现数据完整性的，优先使用约束；

2° 无法通过约束实现的，使用存储过程：存储过程中在确定更新之前先检查；

3° 当1°，2°都不满足时，使用触发器。

MYSQL中的触发器

(1) 语法
```
  CREATE TRIGGER trigger_name 
  trigger_time trigger_event
  ON tbl_name FOR EACH ROW
  [trigger_order]
  trigger_body
```
其中
```
  trigger_time: { BEFORE | AFTER }

  trigger_event: { INSERT | UPDATE | DELETE }

  trigger_order: { FOLLOWS | PRECEDES } other_trigger_name
```
(2) A trigger is a named database object that is associated with a table, and that activates when a particular event occurs for the table.

(3) __触发器必须和一个永久的table关联，不能和一个临时table关联，也不能和视图关联。__The trigger becomes associated with the table named tbl_name, which must refer to a permanent table. You cannot associate a trigger with a TEMPORARY table or a view.

(4) Trigger names exist in the schema namespace, meaning that all triggers must have unique names within a schema. Triggers in different schemas can have the same name.

(5) CREATE TRIGGER requires the TRIGGER privilege for the table associated with the trigger. The statement might also require the SUPER privilege, depending on the DEFINER value, as described later in this section. If binary logging is enabled, CREATE TRIGGER might require the SUPER privilege, as described in Section 23.7, “Binary Logging of Stored Programs”.

(6) trigger_time is the trigger action time. It can be BEFORE or AFTER to indicate that the trigger activates before or after each row to be modified.

(7) 列的值检查发生在触发器之前（即使是BEFORE），所以不能用触发器先进行不合理值的转换。Basic column value checks occur prior to trigger activation, so you cannot use BEFORE triggers to convert values inappropriate for the column type to valid values.

(8) trigger_event indicates the kind of operation that activates the trigger. These trigger_event values are permitted:

INSERT: The trigger activates whenever a new row is inserted into the table; for example, through INSERT, LOAD DATA, and REPLACE statements.

UPDATE: The trigger activates whenever a row is modified; for example, through UPDATE statements.

DELETE: The trigger activates whenever a row is deleted from the table; for example, through DELETE and REPLACE statements. DROP TABLE and TRUNCATE TABLE statements on the table do not activate this trigger, because they do not use DELETE. Dropping a partition does not activate DELETE triggers, either.

(9) The trigger event does not represent a literal type of SQL statement that activates the trigger so much as it represents a type of table operation. For example, an INSERT trigger activates not only for INSERT statements but also LOAD DATA statements because both statements insert rows into a table.

(10) a BEFORE INSERT trigger activates for every row, followed by either an AFTER INSERT trigger or both the BEFORE UPDATE and AFTER UPDATE triggers, depending on whether there was a duplicate key for the row.

(11) 对于同一个table，可能会有在同一个触发事件、同一个触发时机的多个触发器。此时，触发器触发的顺序是按照它们创建的顺序来的。但是可以显示添加 FOLLOWS 或 PRECEDES，用于指定跟随在哪个触发器前面或后面。 It is possible to define multiple triggers for a given table that have the same trigger event and action time. For example, you can have two BEFORE UPDATE triggers for a table. By default, triggers that have the same trigger event and action time activate in the order they were created. To affect trigger order, specify a trigger_order clause that indicates FOLLOWS or PRECEDES and the name of an existing trigger that also has the same trigger event and action time. With FOLLOWS, the new trigger activates after the existing trigger. With PRECEDES, the new trigger activates before the existing trigger.

(12) trigger body is the statement to execute when the trigger activates. To execute multiple statements, use the BEGIN ... END compound statement construct.

(13) 可以用NEW.列名和OLD.列名代表新表的列和旧表的列。 Within the trigger body, you can refer to columns in the subject table (the table associated with the trigger) by using the aliases OLD and NEW. OLD.col_name refers to a column of an existing row before it is updated or deleted. NEW.col_name refers to the column of a new row to be inserted or an existing row after it is updated.

注：Triggers cannot use NEW.col name or use OLD.col name to refer to generated columns. For information about generated columns, see Section 13.1.18.8, “CREATE TABLE and Generated Columns”.

(14) The DEFINER clause specifies the MySQL account to be used when checking access privileges at trigger activation time. If a user value is given, it should be a MySQL account specified as 'user_name'@'host_name', CURRENT_USER, or CURRENT_USER(). The default DEFINER value is the user who executes the CREATE TRIGGER statement. This is the same as specifying DEFINER = CURRENT_USER explicitly.

(15) Within a trigger body, the CURRENT_USER() function returns the account used to check privileges at trigger activation time. This is the DEFINER user, not the user whose actions caused the trigger to be activated.

（16） If you use LOCK TABLES to lock a table that has triggers, the tables used within the trigger are also locked, as described in LOCK TABLES and Triggers.
MYSQL中触发器的语法和示例

(1) 创建触发器
```
  CREATE [DEFINER = { user | CURRENT_USER }]
  TRIGGER trigger_name
  trigger_time trigger_event
  ON tbl_name FOR EACH ROW
  [trigger_order]
  trigger_body
```
其中
```
  trigger_time: { BEFORE | AFTER }

  trigger_event: { INSERT | UPDATE | DELETE }

  trigger_order: { FOLLOWS | PRECEDES } other_trigger_name
```
(2) 删除触发器
```
  DROP TRIGGER [IF EXISTS] [database_name.]trigger_name
```
其中

The schema (database) name is optional. If the schema is omitted, the trigger is dropped from the default schema. DROP TRIGGER requires the TRIGGER privilege for the table associated with the trigger.

(3) 一个简单示例
```
  CREATE TABLE account (
      acct_num INT, 
      amount DECIMAL(10,2));

  CREATE TRIGGER ins_sum BEFORE INSERT ON account
  FOR EACH ROW SET @sum = @sum + NEW.amount;
```
这个示例的trigger_body很简单：FOR EACH ROW SET @sum = @sum + NEW.amount。它的作用是将新插入的amount累加到一个用户变量 @sum 中。

(4) 另一个简单示例

对于同一个表、同一个触发事件、同一个触发时机，可以定义多个触发器，使用PRECEDES和FOLLOWS可以显示指定触发器的触发顺序。
```
  CREATE TRIGGER ins_transaction BEFORE INSERT ON account
  FOR EACH ROW PRECEDES ins_sum
  SET
  @deposits = @deposits + IF(NEW.amount>0,NEW.amount,0),
  @withdrawals = @withdrawals + IF(NEW.amount<0,-NEW.amount,0);
```
这个触发器会在 ins_sum 触发器之前被触发。如果没有PRECEDES的话，它会在ins_sum触发器之后被触发，因为默认是按触发器创建的顺序。

(5) 在 MySQL 5.7.2 之前，一个表不能在同一个触发事件、同一个触发时机定义多个触发器。此时，应该使用 BEGIN ... END

(6) OLD和NEW分别用来代表更新前的表和更新后的表，下面分为几种情况

1° 对于INSERT触发器，只能使用NEW.colname，不能使用OLD.colname（因为没有旧的列)

2° 对于DELETE触发器，只能使用OLD.colname，不能使用NEW.colname（因为没有新的列）

3° 对于UPDATE触发器，NEW.colname和OLD.name 都可以使用，NEW.colname代表更新后记录的某列， OLD.colname代表更新前记录的某列

(7) OLD.xxx应该是只读的，不能更改数据；

NEW.xxx可读可写，对于一个BEFORE触发器，可以用SET NEW.colname = value来改变数据，也就是说使用BEFORE触发器可以改变要插入或更新的数据值；

!!!但是，对于AFTER触发器，使用SET NEW.colname = value改变数据是没有意义的，因为某一行记录的数据已经改变过了

(8) By using the BEGIN ... END construct, you can define a trigger that executes multiple statements. Within the BEGIN block, you also can use other syntax that is permitted within stored routines such as conditionals and loops. However, just as for stored routines, if you use the mysql program to define a trigger that executes multiple statements, it is necessary to redefine the mysql statement delimiter so that you can use the ; statement delimiter within the trigger definition.

示例
```
  delimiter //

  CREATE TRIGGER upd_check BEFORE UPDATE ON account
  FOR EACH ROW
  BEGIN
      IF NEW.amount < 0 THEN
         SET NEW.amount = 0;
      ELSEIF NEW.amount > 100 THEN
         SET NEW.amount = 100;
      END IF;
  END; //

  delimiter ;
```
(9) The trigger cannot use the CALL statement to invoke stored procedures that return data to the client or that use dynamic SQL. (Stored procedures are permitted to return data to the trigger through OUT or INOUT parameters.)

(10) 触发器不能使用事务

(11) 错误处理顺序

1° 如果BEFORE触发器报错，则在记录上的操作不会执行；

2° 无论后续的步骤是否执行（成功），BEFORE触发器都会被触发；

3° 只有 BEFORE触发器和对记录的操作都成功了，AFTER触发器才会执行；

4° 如果是在事务中，触发器的报错也会导致事务回滚

Triggers can contain direct references to tables by name, such as the trigger named testref shown in this example:

(12) 示例

    CREATE TABLE test1(
        a1 INT);

    CREATE TABLE test2(
        a2 INT);

    CREATE TABLE test3(
        a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);

    CREATE TABLE test4(
        a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        b4 INT DEFAULT 0);

    DELIMITER |

    CREATE TRIGGER testref BEFORE INSERT ON test1
    FOR EACH ROW
    BEGIN
        INSERT INTO test2 SET a2 = NEW.a1;
        DELETE FROM test3 WHERE a3 = NEW.a1;
        UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
    END; |

    DELIMITER ;

测试

    INSERT INTO test3 (a3) VALUES
    (NULL), (NULL), (NULL), (NULL), (NULL),
    (NULL), (NULL), (NULL), (NULL), (NULL);

    INSERT INTO test4 (a4) VALUES
    (0), (0), (0), (0), (0), (0), (0), (0), (0), (0);

    INSERT INTO test1 VALUES 
    (1), (3), (1), (7), (1), (8), (4), (4);

    mysql> SELECT * FROM test1;
    +------+
    | a1   |
    +------+
    |    1 |
    |    3 |
    |    1 |
    |    7 |
    |    1 |
    |    8 |
    |    4 |
    |    4 |
    +------+

   mysql> SELECT * FROM test2;
   +------+
   | a2   |
   +------+
   |    1 |
   |    3 |
   |    1 |
   |    7 |
   |    1 |
   |    8 |
   |    4 |
   |    4 |
   +------+

   mysql> SELECT * FROM test3;
   +----+
   | a3 |
   +----+
   |  2 |
   |  5 |
   |  6 |
   |  9 |
   | 10 |
   +----+

   mysql> SELECT * FROM test4;
   +----+------+
   | a4 | b4   |
   +----+------+
   |  1 |    3 |
   |  2 |    0 |
   |  3 |    1 |
   |  4 |    2 |
   |  5 |    0 |
   |  6 |    0 |
   |  7 |    1 |
   |  8 |    1 |
   |  9 |    0 |
   | 10 |    0 |
   +----+------+

使用BEFORE类型的触发器时，可以改变要插入列的值；使用AFTER类型的触发器时，不能改变要插入列的值（事实上，根本无法创建这样的触发器）

示例

  DROP TABLE IF EXISTS t25;

  CREATE TABLE t25 (
      s1 INT NOT NULL UNIQUE, 
      s2 VARCHAR(5),
      PRIMARY KEY (s1)
  );

  DROP TRIGGER IF EXISTS t25_bi;

  DELIMITER //

  CREATE TRIGGER t25_bi
  BEFORE INSERT ON t25
  FOR EACH ROW
  BEGIN
      IF LEFT(NEW.s2, 1) <> 'A' THEN 
          SET NEW.s1 = 0;
          SET NEW.s2 = 'HEHE';
      END IF;
  END; //

  DELIMITER ;

  INSERT INTO t25 VALUES (3, 'HAHA');

  SELECT * FROM t25;

  结果

  s1   s2
  0    HEHE

由于MYSQL不支持 CHECK约束检查，所以可以使用触发器代替

示例

  USE temp;

  DROP TABLE IF EXISTS t25;

  CREATE TABLE t25 (
      s1 INT NOT NULL UNIQUE, 
      s2 VARCHAR(5),
      PRIMARY KEY (s1)
  );

  DROP TRIGGER IF EXISTS t25_bi;

  DELIMITER //

  CREATE TRIGGER t25_bi
  AFTER INSERT ON t25
  FOR EACH ROW
  BEGIN
      IF LEFT(NEW.s2, 1) <> 'A' THEN 
          DELETE FROM t25 WHERE s1 = New.s1;
      END IF;
  END; //

  DELIMITER ;

  INSERT INTO t25 VALUES (3, 'AHA');
  SELECT * FROM t25;

  /*
  INSERT INTO t25 VALUES (5, 'hAHA');
  SELECT * FROM t25;
  */

使用触发器，阻止对某列的更新

示例

  USE temp;

  DROP TABLE IF EXISTS t25;

  CREATE TABLE t25 (
      s1 INT NOT NULL UNIQUE, 
      s2 VARCHAR(5),
      PRIMARY KEY (s1)
  );

  DROP TRIGGER IF EXISTS t25_bi;

  DELIMITER //

  CREATE TRIGGER t25_bi
  BEFORE UPDATE ON t25
  FOR EACH ROW
  BEGIN
      DECLARE msg VARCHAR(255);

      IF NEW.s1 <> OLD.s1 THEN 
          SET msg = CONCAT('MyTriggerError: Trying to modify s1: ', CAST(NEW.s1 AS char));
          SIGNAL SQLSTATE '45000' SET message_text = msg;
      END IF;
  END; //

  DELIMITER ;

  INSERT INTO t25 VALUES (3, 'AHA');
  UPDATE t25 SET s1 = 4 WHERE s1 = 3;
  SELECT * FROM t25;

MYSQL触发器示例

trigger_check.sql

  USE temp;

  DROP TABLE IF EXISTS t25;

  CREATE TABLE t25 (
      s1 INT NOT NULL UNIQUE, 
      s2 VARCHAR(5),
      PRIMARY KEY (s1)
  );

  DROP TRIGGER IF EXISTS t25_bi;

  DELIMITER //

  CREATE TRIGGER t25_bi
  AFTER INSERT ON t25
  FOR EACH ROW
  BEGIN
      IF LEFT(NEW.s2, 1) <> 'A' THEN 
          DELETE FROM t25 WHERE s1 = New.s1;
      END IF;
  END; //

  DELIMITER ;

  INSERT INTO t25 VALUES (3, 'AHA');
  SELECT * FROM t25;

  /*
  INSERT INTO t25 VALUES (5, 'hAHA');
  SELECT * FROM t25;
  */

trigger_modification.sql

  USE temp;

  DROP TABLE IF EXISTS t25;

  CREATE TABLE t25 (
      s1 INT NOT NULL UNIQUE, 
      s2 VARCHAR(5),
      PRIMARY KEY (s1)
  );

  DROP TRIGGER IF EXISTS t25_bi;

  DELIMITER //

  CREATE TRIGGER t25_bi BEFORE UPDATE ON t25 FOR EACH ROW
  BEGIN
      DECLARE msg VARCHAR(255);

      IF NEW.s1 <> OLD.s1 THEN 
          set msg = concat('MyTriggerError: Trying to modify s1: ', cast(new.s1 as char));
          signal sqlstate '45000' set message_text = msg;
       END IF;
  END; //

  DELIMITER ;

  INSERT INTO t25 VALUES (3, 'AHA');
  UPDATE t25 SET s1 = 4 WHERE s1 = 3;
  SELECT * FROM t25;

chapter12_数据库编程_5_触发器

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读