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

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;