あるテーブルに追加(INSERT)/更新(UPDATE)/削除(DELETE)が行われたタイミングで、別のテーブルにデータ操作(追加/更新/削除)を行える機能をトリガーといい、トリガーはAzure上のSQL Database上で作成することができる。
今回は、Azure SQL Database上でトリガーを作成してみたので、そのSQLや実行結果を共有する。
前提条件
以下の記事に従って、Azure SQL Databaseを作成済であること。
![](https://www.purin-it.com/wp-content/uploads/2020/11/adorable-1866530_640-320x180.jpg)
作成済のAzure SQL Databaseの概要を画面表示すると、以下のようになる。
また、下記記事のように、A5M2を利用して作成済のAzure SQL Databaseに接続できていること。
![](https://www.purin-it.com/wp-content/uploads/2022/05/nature-g74d7ea469_640-320x180.jpg)
やってみたこと
USER_DETAILテーブルの追加
トリガーにより更新されるテーブルとして、USER_DETAILテーブルを作成しデータ追加する。その手順は、以下の通り。
1) A5M2でAzure SQL Databaseに接続後、以下のSQLを実行し、USER_DETAILテーブルを作成する。
1 2 3 4 5 6 | CREATE TABLE dbo.USER_DETAIL( ID int not null , NAME nvarchar(40) not null , DETAIL nvarchar(1024) , primary key (ID) ) |
![USER_DETAILテーブルの追加_1](https://www.purin-it.com/wp-content/uploads/2024/03/add_user_detail_1.png)
2) 以下のSQLを実行し、USER_DETAILテーブルにデータ追加する。
1 2 | INSERT INTO dbo.USER_DETAIL VALUES (1, N'テスト プリン1', N'事前登録データ'); INSERT INTO dbo.USER_DETAIL VALUES (2, N'テスト プリン2', N'事前登録データ'); |
![USER_DETAILテーブルの追加_2](https://www.purin-it.com/wp-content/uploads/2024/03/add_user_detail_2.png)
なお、上記INSERT文実行後、オートコミットされるものとする。
3) 以下のSQLを実行し、USER_DETAILテーブルのデータを確認する。
1 | SELECT * FROM dbo.USER_DETAIL |
![USER_DETAILテーブルの追加_3](https://www.purin-it.com/wp-content/uploads/2024/03/add_user_detail_3.png)
4) トリガーを発動するテーブルは、以下のUSER_DATAテーブルとする。そのデータを確認した結果は、以下の通り。
1 | SELECT * FROM dbo.USER_DATA |
![USER_DETAILテーブルの追加_4](https://www.purin-it.com/wp-content/uploads/2024/03/add_user_detail_4.png)
![](https://www.purin-it.com/wp-content/uploads/2021/11/aurora-gcb7e36182_640-320x180.jpg)
トリガーの作成
USER_DATAテーブルにデータを追加/更新/削除したタイミングで、USER_DETAILテーブルのデータを追加/更新/削除されるようなトリガーを作成する。そのSQLは、以下の通り。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | CREATE OR ALTER TRIGGER dbo.MOD_USER_DETAIL -- USER_DATAテーブルに追加・更新・削除が発生した直後にトリガーを発動 ON dbo.USER_DATA AFTER INSERT, UPDATE, DELETE AS BEGIN -- USER_DATAテーブルから削除したデータを、USER_DETAILテーブルから削除 -- deleted:USER_DATAテーブルから削除したデータ -- USER_DATAテーブルで、DELETE文またはUPDATE文実行後に、下記SQLを実行 DELETE FROM dbo.USER_DETAIL WHERE ID = (select ID from deleted); -- USER_DATAテーブルに追加したデータを、USER_DETAILテーブルに追加 -- inserted:USER_DATAテーブルに追加したデータ -- USER_DATAテーブルで、INSERT文またはUPDATE文実行後に、下記SQLを実行 INSERT INTO dbo.USER_DETAIL ( ID, NAME, DETAIL ) SELECT ID, NAME, NULL FROM inserted; END |
なお、insertedテーブル、deletedテーブルについては、以下のサイトを参照のこと。
https://learn.microsoft.com/ja-jp/sql/relational-databases/triggers/use-the-inserted-and-deleted-tables?view=sql-server-ver16
上記SQLを実行するには、以下のように、プロシージャモードで実行する必要がある。
![トリガーの作成_1_2](https://www.purin-it.com/wp-content/uploads/2024/03/add_trigger_1_2.png)
さらに、作成したトリガーは、以下のSQLを実行することで確認できる。
1 2 3 4 | SELECT t.name AS 'トリガー名', s.definition AS 'トリガーSQL' FROM sys.triggers t INNER JOIN sys.all_sql_modules s ON s.object_id = t.object_id WHERE s.object_id = OBJECT_ID(N'dbo.MOD_USER_DETAIL') |
![トリガーの作成_2](https://www.purin-it.com/wp-content/uploads/2024/03/add_trigger_2.png)
なお、トリガーを出力するSQLは、以下のサイトの内容を参考にしている。
https://notepad-blog.com/content/107/
また、上記で出力された「トリガーSQL」は、以下のように、コピーして貼り付けることで再現できる。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | CREATE TRIGGER dbo.MOD_USER_DETAIL -- USER_DATAテーブルに追加・更新・削除が発生した直後にトリガーを発動 ON dbo.USER_DATA AFTER INSERT, UPDATE, DELETE AS BEGIN -- USER_DATAテーブルから削除したデータを、USER_DETAILテーブルから削除 -- deleted:USER_DATAテーブルから削除したデータ -- USER_DATAテーブルで、DELETE文またはUPDATE文実行後に、下記SQLを実行 DELETE FROM dbo.USER_DETAIL WHERE ID = (select ID from deleted); -- USER_DATAテーブルに追加したデータを、USER_DETAILテーブルに追加 -- inserted:USER_DATAテーブルに追加したデータ -- USER_DATAテーブルで、INSERT文またはUPDATE文実行後に、下記SQLを実行 INSERT INTO dbo.USER_DETAIL ( ID, NAME, DETAIL ) SELECT ID, NAME, NULL FROM inserted; END |
![](https://www.purin-it.com/wp-content/uploads/2022/03/dark-gbb97f8f0f_640-320x180.png)
トリガーによるテーブル更新確認
トリガーが発動するUSER_DATAテーブルに、追加・削除・更新が行った場合の実行結果は、以下の通り。なお、INSERT/DELETE/UPDATE文実行後、オートコミットされるものとする。
1) USER_DATAテーブル、USER_DETAILテーブルの内容は、以下の通りとする。
1 | select * from dbo.USER_DATA |
![トリガーによるテーブル更新確認_1_1](https://www.purin-it.com/wp-content/uploads/2024/03/exec_trigger_1_1.png)
1 | select * from dbo.USER_DETAIL |
![トリガーによるテーブル更新確認_1_2](https://www.purin-it.com/wp-content/uploads/2024/03/exec_trigger_1_2.png)
2) USER_DATAテーブルにデータを1件追加する。
1 2 | insert into dbo.USER_DATA values (3, N'テスト プリン3', 2000, 12, 22, '2', N'テストトリガー', 0) |
![トリガーによるテーブル更新確認_2](https://www.purin-it.com/wp-content/uploads/2024/03/exec_trigger_2.png)
3) 2)実行後のUSER_DATAテーブル、USER_DETAILテーブルの内容は以下の通りで、トリガーの影響でUSER_DETAILテーブルにデータ追加されたことが確認できる。
1 | select * from dbo.USER_DATA |
![トリガーによるテーブル更新確認_3_1](https://www.purin-it.com/wp-content/uploads/2024/03/exec_trigger_3_1.png)
1 | select * from dbo.USER_DETAIL |
![トリガーによるテーブル更新確認_3_2](https://www.purin-it.com/wp-content/uploads/2024/03/exec_trigger_3_2.png)
4) USER_DATAテーブルからデータを1件削除する。
1 | delete from dbo.USER_DATA where id = 3 |
![トリガーによるテーブル更新確認_4](https://www.purin-it.com/wp-content/uploads/2024/03/exec_trigger_4.png)
5) 4)実行後のUSER_DATAテーブル、USER_DETAILテーブルの内容は以下の通りで、トリガーの影響でUSER_DETAILテーブルからデータ削除されたことが確認できる。
1 | select * from dbo.USER_DATA |
![トリガーによるテーブル更新確認_5_1](https://www.purin-it.com/wp-content/uploads/2024/03/exec_trigger_5_1.png)
1 | select * from dbo.USER_DETAIL |
![トリガーによるテーブル更新確認_5_2](https://www.purin-it.com/wp-content/uploads/2024/03/exec_trigger_5_2.png)
6) USER_DATAテーブルのデータを1件更新する。
1 | update dbo.USER_DATA set name = N'テスト プリン2 更新' where id = 2 |
![トリガーによるテーブル更新確認_6](https://www.purin-it.com/wp-content/uploads/2024/03/exec_trigger_6.png)
7) 6)実行後のUSER_DATAテーブル、USER_DETAILテーブルの内容は以下の通りで、トリガーの影響でUSER_DETAILテーブルのデータが更新(削除後に追加)されたことが確認できる。
1 | select * from dbo.USER_DATA |
![トリガーによるテーブル更新確認_7_1](https://www.purin-it.com/wp-content/uploads/2024/03/exec_trigger_7_1.png)
1 | select * from dbo.USER_DETAIL |
![トリガーによるテーブル更新確認_7_2](https://www.purin-it.com/wp-content/uploads/2024/03/exec_trigger_7_2.png)
トリガーの削除
トリガーの削除は、DROP TRIGGER文で行える。
1 | DROP TRIGGER dbo.MOD_USER_DETAIL |
![トリガーの削除_1](https://www.purin-it.com/wp-content/uploads/2024/03/del_trigger_1.png)
さらに、指定したトリガーが削除されたかどうかは、以下のSQLを実行することで確認できる。
1 2 3 4 | SELECT t.name AS 'トリガー名', s.definition AS 'トリガーSQL' FROM sys.triggers t INNER JOIN sys.all_sql_modules s ON s.object_id = t.object_id WHERE s.object_id = OBJECT_ID(N'dbo.MOD_USER_DETAIL') |
![トリガーの削除_2](https://www.purin-it.com/wp-content/uploads/2024/03/del_trigger_2.png)
試しに、トリガーが削除された状態で、USER_DATAテーブルにデータを1件追加する。
1 2 | insert into dbo.USER_DATA values (3, N'テスト プリン3', 2000, 12, 22, '2', N'テストトリガー', 0) |
![トリガーの削除_3](https://www.purin-it.com/wp-content/uploads/2024/03/del_trigger_3.png)
実行後のUSER_DATAテーブル、USER_DETAILテーブルの内容は以下の通りで、トリガーが無いのでUSER_DETAILテーブルが更新されないことが確認できる。
1 | select * from dbo.USER_DATA |
![トリガーの削除_4_1](https://www.purin-it.com/wp-content/uploads/2024/03/del_trigger_4_1.png)
1 | select * from dbo.USER_DETAIL |
![トリガーの削除_4_2](https://www.purin-it.com/wp-content/uploads/2024/03/del_trigger_4_2.png)
要点まとめ
- あるテーブルに追加(INSERT)/更新(UPDATE)/削除(DELETE)が行われたタイミングで、別のテーブルにデータ操作(追加/更新/削除)を行える機能をトリガーという。
- Azure上のSQL Database上で、トリガーを作成できる。