欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

MySQL的trigger和oracle的trigger的区别

程序员文章站 2024-02-17 22:46:04
...

MySQL的trigger是仅仅statement激活,不能对同一table的同一个event写两个trigger, 同一个event是指create trigger 中的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)

MySQL的trigger和oracle的trigger的区别