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