Solaris User

トリガーとは [MySQL]

トリガーの作成

構文：
CREATE
[DEFINER = { user | CURRENT_USER }]
TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_stmt

trigger_name： トリガー名（任意）
trigger_time： BEFOREもしくはAFTERを指定
trigger_event： INSERT、UPDATE、DELETEを指定可能
tbl_name： 対象テーブル名
trigger_stmt： トリガーを有効化させるときに実行するステートメント

実行例

mysql> CREATE TABLE test_trigger (column1 INT);
Query OK, 0 rows affected (0.08 sec)

mysql> CREATE TABLE audit_trigger
    -> (old_column INT, new_column INT, date_completed DATETIME);
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE TRIGGER trigger_ai AFTER INSERT ON test_trigger
    -> FOR EACH ROW
    -> INSERT INTO audit_trigger (new_column, date_completed)
    -> VALUES (NEW.column1, NOW());
Query OK, 0 rows affected (0.44 sec)

mysql> INSERT INTO test_trigger VALUES (1), (2), (3), (4), (5), (6);
Query OK, 6 rows affected (0.06 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test_trigger;
+---------+
| column1 |
+---------+
|       1 |
|       2 |
|       3 |
|       4 |
|       5 |
|       6 |
+---------+
6 rows in set (0.00 sec)

mysql> SELECT * FROM audit_trigger;
+------------+------------+---------------------+
| old_column | new_column | date_completed      |
+------------+------------+---------------------+
|       NULL |          1 | 2009-06-25 11:29:26 |
|       NULL |          2 | 2009-06-25 11:29:26 |
|       NULL |          3 | 2009-06-25 11:29:26 |
|       NULL |          4 | 2009-06-25 11:29:26 |
|       NULL |          5 | 2009-06-25 11:29:26 |
|       NULL |          6 | 2009-06-25 11:29:26 |
+------------+------------+---------------------+
6 rows in set (0.00 sec)

mysql> CREATE TRIGGER torigger_au AFTER UPDATE ON test_trigger
    -> FOR EACH ROW
    -> INSERT INTO audit_trigger (old_column, new_column, date_completed)
    -> VALUE (OLD.column1, NEW.column1,NOW());
Query OK, 0 rows affected (0.09 sec)

mysql> UPDATE test_trigger SET column1=10 WHERE column1 < 3;
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> SELECT * FROM test_trigger;
+---------+
| column1 |
+---------+
|      10 |
|      10 |
|       3 |
|       4 |
|       5 |
|       6 |
+---------+
6 rows in set (0.00 sec)

mysql> SELECT * FROM audit_trigger;
+------------+------------+---------------------+
| old_column | new_column | date_completed      |
+------------+------------+---------------------+
|       NULL |          1 | 2009-06-25 11:29:26 |
|       NULL |          2 | 2009-06-25 11:29:26 |
|       NULL |          3 | 2009-06-25 11:29:26 |
|       NULL |          4 | 2009-06-25 11:29:26 |
|       NULL |          5 | 2009-06-25 11:29:26 |
|       NULL |          6 | 2009-06-25 11:29:26 |
|          1 |         10 | 2009-06-25 11:36:14 |
|          2 |         10 | 2009-06-25 11:36:14 |
+------------+------------+---------------------+
8 rows in set (0.00 sec)

mysql> CREATE TRIGGER trigger_ad AFTER DELETE ON test_trigger
    -> FOR EACH ROW
    -> INSERT INTO audit_trigger(old_column, date_completed)
    -> VALUES (OLD.column1, NOW());
Query OK, 0 rows affected (0.47 sec)

mysql> DELETE FROM test_trigger WHERE column1 >2 AND column1 < 10;
Query OK, 4 rows affected (0.00 sec)

mysql> SELECT * FROM test_trigger;
+---------+
| column1 |
+---------+
|      10 |
|      10 |
+---------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM audit_trigger;
+------------+------------+---------------------+
| old_column | new_column | date_completed      |
+------------+------------+---------------------+
|       NULL |          1 | 2009-06-25 11:29:26 |
|       NULL |          2 | 2009-06-25 11:29:26 |
|       NULL |          3 | 2009-06-25 11:29:26 |
|       NULL |          4 | 2009-06-25 11:29:26 |
|       NULL |          5 | 2009-06-25 11:29:26 |
|       NULL |          6 | 2009-06-25 11:29:26 |
|          1 |         10 | 2009-06-25 11:36:14 |
|          2 |         10 | 2009-06-25 11:36:14 |
|          3 |       NULL | 2009-06-25 11:39:52 |
|          4 |       NULL | 2009-06-25 11:39:52 |
|          5 |       NULL | 2009-06-25 11:39:52 |
|          6 |       NULL | 2009-06-25 11:39:52 |
+------------+------------+---------------------+
12 rows in set (0.00 sec)

mysql> DROP TRIGGER trigger_ad;
Query OK, 0 rows affected (0.08 sec)

mysql> DELETE FROM test_trigger;
Query OK, 2 rows affected (0.00 sec)

mysql> SELECT * FROM test_trigger;
Empty set (0.00 sec)

mysql> SELECT * FROM audit_trigger;
+------------+------------+---------------------+
| old_column | new_column | date_completed      |
+------------+------------+---------------------+
|       NULL |          1 | 2009-06-25 11:29:26 |
|       NULL |          2 | 2009-06-25 11:29:26 |
|       NULL |          3 | 2009-06-25 11:29:26 |
|       NULL |          4 | 2009-06-25 11:29:26 |
|       NULL |          5 | 2009-06-25 11:29:26 |
|       NULL |          6 | 2009-06-25 11:29:26 |
|          1 |         10 | 2009-06-25 11:36:14 |
|          2 |         10 | 2009-06-25 11:36:14 |
|          3 |       NULL | 2009-06-25 11:39:52 |
|          4 |       NULL | 2009-06-25 11:39:52 |
|          5 |       NULL | 2009-06-25 11:39:52 |
|          6 |       NULL | 2009-06-25 11:39:52 |
+------------+------------+---------------------+
12 rows in set (0.00 sec)

オラクルユニバーシティSolaris系技術トレーニング一覧

• OpenStack Administration Using Oracle Solaris 11 NEW
• Oracle ZFS Storage Appliance Administration NEW
• Oracle Solaris 11 新機能
• Oracle Solaris 11 システム管理 I
• Oracle Solaris 11 システム管理 II
• Oracle Solaris 11 パフォーマンス管理
• Oracle Solaris 11 セキュリティ管理
• Oracle Solaris 11 ZFS 管理
• Oracle Solaris 11 ゾーン管理
• Oracle Solaris 11 トラブルシューティング
• Oracle VM Server for SPARC: Installation and Configuration

Solarisお勧め書籍

Solaris 11.2 システムハンドブック

Oracle Solaris 11 試験対策本(OCA)