MySQL 触发器使用入门

关于触发器的内容

格式

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

一个复杂的例子：

delimiter //
create trigger atrigger
after insert 
on borrow for each row
begin
    declare msg varchar(20);
    set msg='stock = 0';
    if( 
        (select stock 
    	from book
    	where book.bno=NEW.bno)=0) 
    	then
      signal sqlstate '45000' set message_text=msg;
      delete from borrow where bno=NEW.bno;
    else 
      update book set stock=stock-1
      where book.bno=NEW.bno;
    end if;
end //

教程

Easy Example

以下是一个将触发器与表相关联的简单示例，该操作作用在 INSERT。

这个触发器充当累加器，将插入到表的一列中的值相加。

mysql> CREATE TABLE account (
    acct_num INT,
    amount DECIMAL(10,2));


mysql> CREATE TRIGGER ins_sum 
	   BEFORE INSERT ON account
       FOR EACH ROW
       SET @sum = @sum + NEW.amount;

CREATE TRIGGER 语句创建一个名为 ins_sum 的触发器，该触发器与帐户表相关联。

• 关键字 BEFORE 表示触发动作时间。在这种情况下，触发器会在每一行插入表之前激活。与之相对的关键字是 AFTER。
• 关键字 INSERT 表示触发事件；即激活触发器的操作类型。在示例中，INSERT 操作导致触发器激活。我们还可以为 DELETE 和 UPDATE 操作创建触发器。
• FOR EACH ROW 之后的语句定义了触发器主体；即，每次触发器激活时执行的语句，对于受触发事件影响的每一行，该语句发生一次。在示例中，触发器主体是一个简单的 SET，它将插入到数量列中的值累积到用户变量中。该语句将该列称为 NEW.amount，这意味着“要插入新行的amount的值”。

先将触发器的累加器变量设置为零，执行 INSERT 语句，然后查看变量的值：

mysql> insert into account
    -> values(137,14.98),(141,1937.50),(97,-100.0);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select @sum as 'Total amount inserted'
    -> ;
+-----------------------+
| Total amount inserted |
+-----------------------+
|               1852.48 |
+-----------------------+
1 row in set (0.00 sec)

要销毁触发器，请使用 DROP TRIGGER 语句:

mysql> DROP TRIGGER study.ins_sum;

进阶

我们可以为给定表定义多个触发器，这些触发器具有相同的触发器事件和操作时间。例如，一个表可以有两个 BEFORE UPDATE 触发器。

默认情况下，具有相同触发事件和操作时间的触发器按创建顺序激活。

触发器顺序

要影响触发器顺序，请在 FOR EACH ROW 之后指定一个子句，指示 FOLLOWS 或PRECEDES 以及也具有相同触发器事件和操作时间的现有触发器的名称。

• 使用 FOLLOWS，新触发器在现有触发器之后激活。
• 使用 PRECEDES，新触发器在现有触发器之前激活。

mysql> 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);
Query OK, 0 rows affected (0.02 sec)
# if的格式 IF(condition,value,else-value)

这个触发器 ins_transaction 与 ins_sum 类似，但分别累加 存款和取款。它有一个 PRECEDES 子句，使它在 ins_sum 之前激活；如果没有该子句，它将在 ins_sum 之后激活，因为它是在 ins_sum 之后创建的。

在触发器正文中，OLD 和 NEW 关键字使您能够访问受触发器影响的行中的列。 OLD 和 NEW 是 MySQL 对触发器的扩展；它们不区分大小写。

在 INSERT 触发器中，只能使用 NEW.col_name。在 DELETE 触发器中，只能使用 OLD.col_name。在 UPDATE触发器中，可以使用 OLD.col_name 来引用行更新之前的列，使用 NEW.col_name 来引用行更新后的列。

多个语句的触发器

通过使用 BEGIN ... END 结构，可以定义执行多个语句的触发器。

在 BEGIN 块中，可以使用存储例程中允许的其他语法，例如条件和循环。

但是，如果你使用mysql程序定义了一个执行多条语句的触发器，就需要重新定义mysql语句分隔符，这样你才可以使用;触发器定义中的语句分隔符。

Example

定义一个 UPDATE 触发器，该触发器检查用于更新每一行的新值，并将该值修改为 0 到 100 的范围内。这必须是一个BEFORE触发器，因为该值必须在用于更新之前进行检查行：

mysql> delimiter //
mysql> 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;//
mysql> delimiter ;

补充资料：Mysql变量