mysql is after_mysql触发器Before和After的区别

展开全部

oracle触发器格式：

在CODE上查看代码片派生e68a84e8a2ad3231313335323631343130323136353331333363363461到我的代码片

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触发器格式：

在CODE上查看代码片派生到我的代码片

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)：

在CODE上查看代码片派生到我的代码片

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客户端设置的自动提交)

在CODE上查看代码片派生到我的代码片

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

在CODE上查看代码片派生到我的代码片

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)时，不需要修改触发器)

在CODE上查看代码片派生到我的代码片

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：声明位置在触发时的执行语句块内部

在CODE上查看代码片派生到我的代码片

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块中至少包含一条可执行语句)

在CODE上查看代码片派生到我的代码片

CREATE OR REPLACE TRIGGER trigger_test_insert

BEFORE INSERT ON test

FOR EACH ROW

BEGIN

--just a test

/* just a test */

null;

END;

/

MySQL：使用/* */作为注释符，或者两个连续的-后加一个空格作为注释符

在CODE上查看代码片派生到我的代码片

delimiter $

CREATE TRIGGER trigger_test_insert

BEFORE INSERT ON test

FOR EACH ROW

BEGIN

/* just a test */

-- 两个‘-’后面必须带空格

END$

delimiter ;

4，赋值语法不同

Oracle：可以通过select into语句赋值，还可以通过:=进行赋值

在CODE上查看代码片派生到我的代码片

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语句进行赋值

在CODE上查看代码片派生到我的代码片

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表示

在CODE上查看代码片派生到我的代码片

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表示

在CODE上查看代码片派生到我的代码片

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()指代系统时间

本回答由提问者推荐

已赞过

已踩过<

你对这个回答的评价是？

评论

收起