Oracle触发器和MySQL触发器之间的区别

bsajvcgaax

　　Oracle触发器格式：
　　

CREATE [OR REPLACE] TRIGGER trigger_name
BEFORE|AFTER INSERT|UPDATE|DELETE ON table_name
[FOR EACH ROW]
DECLARE arg_name type [CONSTANT] [NOT NULL] [:=value]
BEGIN
pl/sql语句
END
　　
　　
　　
　　MySQL触发器格式：
　　

CREATE TRIGGER trigger_name
BEFORE|AFTER INSERT|UPDATE|DELETE ON table_name
[FOR EACH ROW]
BEGIN
DECLARE arg_name1[,arg_name2,...] type [DEFAULT value]
sql语句
END

 

创建测试表（建表语句适用于Oracle、MySQL）：

CREATE TABLE test(
id            int,
name          varchar(10),
age           int,
birthday      date,
description   varchar(50),
PRIMARY KEY (id)
);
CREATE TABLE test_log(
id            int,
dealtime      date,
dealtype      varchar(10),
PRIMARY KEY (`id`)
);
 
　　
　　Oracle触发器和MySQL触发器的区别如下：
　　
　　1，创建语句格式不同
　　
　　Oracle：create or replace（Oracle客户端需要手动提交，MySQL客户端设置的自动提交）

SQL> CREATE OR REPLACE TRIGGER trigger_test_insert
2         BEFORE INSERT ON test
3         FOR EACH ROW
4  BEGIN
5         insert into test_log values(1,sysdate,'insert');
6  END;
7  /
Trigger created
SQL> insert into test(id, name) values(1, 'name');
1 row inserted
SQL> commit;
Commit complete
SQL> select * from test_log;
ID DEALTIME    DEALTYPE
--------------------------------------- ----------- ----------
1 2014/7/16 1 insert
　　
　　
　　MySQL：不包含or replace

mysql> delimiter $
CREATE TRIGGER trigger_test_insert  
BEFORE INSERT ON test
FOR EACH ROW
BEGIN
insert into test_log values(1,now(),'insert');
END$
delimiter ;
Query OK, 0 rows affected
mysql> insert into test(id, name) values(1, 'name');
Query OK, 1 row affected
mysql> select * from test_log;
+----+------------+----------+
| id | dealtime   | dealtype |
+----+------------+----------+
|  1 | 2014-07-16 | insert   |
+----+------------+----------+
1 row in set
　　
　　
　　2，变量的声明位置、声明格式均不相同
　　
　　Oracle：声明位置在触发时的执行语句块外部
　　
　　通过%type的方式将变量与表特定字段类型相关联的好处是：在某些情况下，修改该字段类型时不需要修改触发器（如：字段类型由varchar(10)修改为varchar(20)时，不需要修改触发器）

SQL> CREATE TRIGGER trigger_test_insert
2         BEFORE INSERT ON test
3         FOR EACH ROW
4  DECLARE id1 int default 1;
5          id2 int:=1;
6          id3 test_log.id%type:=1;
7  BEGIN
8          insert into test_log values(id1+id2+id3,sysdate,'insert');
9  END;
10  /
Trigger created
SQL> insert into test(id, name) values(1, 'name');
1 row inserted
SQL> commit;
Commit complete
SQL> select * from test_log;
ID DEALTIME    DEALTYPE
--------------------------------------- ----------- ----------
3 2014/7/16 1 insert
　　
　　
　　MySQL：声明位置在触发时的执行语句块内部

mysql> delimiter $
CREATE TRIGGER trigger_test_insert  
BEFORE INSERT ON test
FOR EACH ROW
BEGIN
DECLARE id1 int DEFAULT 1;
DECLARE id2 int DEFAULT 1;
insert into test_log values(id1+id2,now(),'insert');
END$
delimiter ;
Query OK, 0 rows affected
mysql> insert into test(id, name) values(1, 'name');
Query OK, 1 row affected
mysql> select * from test_log;
+----+------------+----------+
| id | dealtime   | dealtype |
+----+------------+----------+
|  2 | 2014-07-16 | insert   |
+----+------------+----------+
1 row in set
　　
　　
　　3，注释符不同
　　
　　Oracle：使用/* */作为注释符，或者两个连续的-作为注释符（PL/SQL块中至少包含一条可执行语句）

CREATE OR REPLACE TRIGGER trigger_test_insert  
BEFORE INSERT ON test
FOR EACH ROW
BEGIN
--just a test
/* just a test */
null;
END;
/
　　
　　
　　MySQL：使用/* */作为注释符，或者两个连续的-后加一个空格作为注释符

delimiter $
CREATE TRIGGER trigger_test_insert  
BEFORE INSERT ON test
FOR EACH ROW
BEGIN
/* just a test */
-- 两个‘-’后面必须带空格
END$
delimiter ;
　　 
　　
　　4，赋值语法不同
　　
　　Oracle：可以通过select into语句赋值，还可以通过:=进行赋值

SQL> CREATE OR REPLACE TRIGGER trigger_test_insert
2  BEFORE INSERT ON test
3  FOR EACH ROW
4  DECLARE id int;
5  BEGIN
6  select max(tl.id) into id from test_log tl;
7  if id is null then
8  id:=1;
9  else
10  id:=id+1;
11  end if;
12  insert into test_log values(id,sysdate,'insert');
13  END;
14  /
Trigger created
SQL> insert into test(id, name) values(1, 'name');
1 row inserted
SQL> commit;
Commit complete
SQL> select * from test_log;
ID DEALTIME    DEALTYPE
--------------------------------------- ----------- ----------
1 2014/7/16 1 insert
　　
　　
　　MySQL：可以通过select into语句赋值，还可以通过set语句进行赋值

mysql> delimiter $
CREATE TRIGGER trigger_test_insert  
BEFORE INSERT ON test
FOR EACH ROW
BEGIN
DECLARE id int;
select max(tl.id) into id from test_log tl;
if id is null then
set id=1;
else
set id=id+1;
end if;
insert into test_log values(id,now(),'insert');
END$
delimiter ;
Query OK, 0 rows affected
mysql> insert into test(id, name) values(1, 'name');
Query OK, 1 row affected
mysql> select * from test_log;
+----+------------+----------+
| id | dealtime   | dealtype |
+----+------------+----------+
|  1 | 2014-07-16 | insert   |
+----+------------+----------+
1 row in set
　　
　　
　　5，对于行级更新触发器
　　
　　Oracle：原有行用:old表示，新行用:new表示

SQL> CREATE OR REPLACE TRIGGER trigger_test_update
2         BEFORE UPDATE ON test
3         FOR EACH ROW
4  BEGIN
5         :new.description := 'change name[' ||
6                          :old.name || ']->[' ||
7                          :new.name || ']';
8  END;
9  /
Trigger created
SQL> insert into test(id, name) values (1, 'aaa');
1 row inserted
SQL> commit;
Commit complete
SQL> update test set name = 'bbb' where id = 1;
1 row updated
SQL> commit;
Commit complete
SQL> select id, name, description from test;
ID NAME       DESCRIPTION
--------------------------------------- ---------- ----------------------                                      
1 bbb        change name[aaa]->[bbb]
　　
　　
　　MySQL：原有行用old表示，新行用new表示

mysql> delimiter $
CREATE TRIGGER trigger_test_update  
BEFORE UPDATE ON test
FOR EACH ROW
BEGIN
set new.description = concat('change name[',
old.name,']->[',new.name,']');
END$
delimiter ;
Query OK, 0 rows affected
mysql> insert into test(id, name) values (1, 'aaa');
Query OK, 1 row affected
mysql> update test set name = 'bbb' where id = 1;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select id, name, description from test;
+----+------+-------------------------+
| id | name | description             |
+----+------+-------------------------+
|  1 | bbb  | change name[aaa]->[bbb] |
+----+------+-------------------------+
1 row in set
　　
　　
　　6，其它一些语法、函数上的区别
　　
　　Oracle：使用if...elsif...else
　　
　　MySQL：使用if...elseif...else
　　
　　Oracle：sysdate指代系统时间
　　
　　MySQL：sysdate()指代系统时间