MySQL的trigger和oracle的trigger的区别

MySQL的trigger和oracle的trigger的区别

MySQL的trigger是仅仅statement激活，不能对同一table的同一个event写两个trigger, 同一个event是指create trigger 中的trigger_time+trigger_event. trigger不会因为某些表的改变而被激活，这些表的改变是通过API修改的，这些API不发送语句给mysql server.

1. mysql只能对每一行触发一次，而Oracle可以对每一行整个表触发一次。
2. mysql只能是表的行激活触发器，但oracle可以做到对列触发。

oracle里面触发器的定义语句：

CREATE
[OR REPLACE] trigger_name trigger_time trigger_event of column
ON tbl_name referencing old as old_value
new as new_value
FOR EACH ROW trigger_stmt

mysql中触发器的定义语句：
CREATE
[DEFINER = { user | CURRENT_USER }]
TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_stmt

简单测试如下：

mysql> delimiter |
mysql> CREATE TRIGGER test_a BEFORE INSERT ON a
    ->   FOR EACH ROW BEGIN
    ->     INSERT INTO abc SET id = NEW.id;
    ->
    ->   END;
    -> |
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql>
mysql>
mysql> delimiter |
mysql> CREATE TRIGGER test_b BEFORE INSERT ON a
    ->   FOR EACH ROW BEGIN
    ->     INSERT INTO abc SET name = NEW.name;
    ->
    ->   END;
    -> |
ERROR 1235 (42000): This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'
mysql> delimiter ;
mysql>
mysql>
mysql> delimiter |
mysql> CREATE TRIGGER test_b AFTER INSERT ON a
    ->   FOR EACH ROW BEGIN
    ->     INSERT INTO abc SET name = NEW.name;
    ->
    ->   END;
    -> |
Query OK, 0 rows affected (0.00 sec)