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;
 
                                     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:/**/をアノテーションとして、または2つの連続-をアノテーションとして使用します(PL/SQLブロックには少なくとも1つの実行可能文が含まれています).
CREATE OR REPLACE TRIGGER trigger_test_insert  
	BEFORE INSERT ON test 
	FOR EACH ROW
BEGIN
	--just a test
  /* just a test */
	null;
END;
/

MySQL:コメントとして/**/を使用するか、連続する2つ-コメントとしてスペースを追加
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()はシステム時間を指す