Azure SQL Database上でトリガーを作成してみた｜ITエンジニアとして経験・学習したこと

あるテーブルに追加(INSERT)／更新(UPDATE)／削除(DELETE)が行われたタイミングで、別のテーブルにデータ操作(追加／更新／削除)を行える機能をトリガーといい、トリガーはAzure上のSQL Database上で作成することができる。

今回は、Azure SQL Database上でトリガーを作成してみたので、そのSQLや実行結果を共有する。

前提条件

以下の記事に従って、Azure SQL Databaseを作成済であること。

Azure Potal上でSQLデータベースを作成してみたAzure Portal上では、常に最新の安定したバージョンの SQL Serverデータベースである「SQL データベース」が利用でき...

作成済のAzure SQL Databaseの概要を画面表示すると、以下のようになる。

また、下記記事のように、A5M2を利用して作成済のAzure SQL Databaseに接続できていること。

A5M2を利用して各DBに接続してみたA5M2(A5:SQL Mk-2)は、SQL文の入力支援やER図作成などの機能を備えていて、Oracle、MySQL、PostgreSQ...

USER_DETAILテーブルの追加

トリガーにより更新されるテーブルとして、USER_DETAILテーブルを作成しデータ追加する。その手順は、以下の通り。

1) A5M2でAzure SQL Databaseに接続後、以下のSQLを実行し、USER_DETAILテーブルを作成する。

CREATE TABLE dbo.USER_DETAIL(
  ID int not null
  , NAME nvarchar(40) not null
  , DETAIL nvarchar(1024)
  , primary key (ID)
)

CREATE TABLE dbo.USER_DETAIL(

ID int not null

, NAME nvarchar(40) not null

, DETAIL nvarchar(1024)

, primary key (ID)

)

2) 以下のSQLを実行し、USER_DETAILテーブルにデータ追加する。

INSERT INTO dbo.USER_DETAIL VALUES (1, N'テスト　プリン１', N'事前登録データ');
INSERT INTO dbo.USER_DETAIL VALUES (2, N'テスト　プリン２', N'事前登録データ');

1 2	INSERT INTO dbo.USER_DETAIL VALUES (1, N'テスト　プリン１', N'事前登録データ'); INSERT INTO dbo.USER_DETAIL VALUES (2, N'テスト　プリン２', N'事前登録データ');

なお、上記INSERT文実行後、オートコミットされるものとする。

3) 以下のSQLを実行し、USER_DETAILテーブルのデータを確認する。

SELECT * FROM dbo.USER_DETAIL

1	SELECT * FROM dbo.USER_DETAIL

4) トリガーを発動するテーブルは、以下のUSER_DATAテーブルとする。そのデータを確認した結果は、以下の通り。

SELECT * FROM dbo.USER_DATA

1	SELECT * FROM dbo.USER_DATA

ウズウズカレッジJavaコースはわかりやすい動画教材と充実した就業サポートで優良企業を目指せるプログラミングスクールだったJavaは、世界中で広く使われていて、現在の需要が高く将来性もある開発言語になります。 https://www.acrovision....

トリガーの作成

USER_DATAテーブルにデータを追加／更新／削除したタイミングで、USER_DETAILテーブルのデータを追加／更新／削除されるようなトリガーを作成する。そのSQLは、以下の通り。

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

CREATE OR ALTER TRIGGER dbo.MOD_USER_DETAIL

-- USER_DATAテーブルに追加・更新・削除が発生した直後にトリガーを発動

ON dbo.USER_DATA

AFTER INSERT, UPDATE, DELETE

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を実行するには、以下のように、プロシージャモードで実行する必要がある。

さらに、作成したトリガーは、以下のSQLを実行することで確認できる。

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')

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')

なお、トリガーを出力するSQLは、以下のサイトの内容を参考にしている。
https://notepad-blog.com/content/107/

また、上記で出力された「トリガーSQL」は、以下のように、コピーして貼り付けることで再現できる。

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

CREATE TRIGGER dbo.MOD_USER_DETAIL

-- USER_DATAテーブルに追加・更新・削除が発生した直後にトリガーを発動

ON dbo.USER_DATA

AFTER INSERT, UPDATE, DELETE

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

【PR】「Filmora」は初心者でも本格的な動画編集ができる大変便利なツールだった「Filmora」は初心者でも使いやすい動画編集ツールで、テンプレートとして利用できるテキスト・動画・音楽などが充実していると共に、複数...

トリガーによるテーブル更新確認

トリガーが発動するUSER_DATAテーブルに、追加・削除・更新が行った場合の実行結果は、以下の通り。なお、INSERT/DELETE/UPDATE文実行後、オートコミットされるものとする。

1) USER_DATAテーブル、USER_DETAILテーブルの内容は、以下の通りとする。

select * from dbo.USER_DATA

1	select * from dbo.USER_DATA

select * from dbo.USER_DETAIL

1	select * from dbo.USER_DETAIL

2) USER_DATAテーブルにデータを1件追加する。

insert into dbo.USER_DATA 
values (3, N'テスト　プリン３', 2000, 12, 22, '2', N'テストトリガー', 0)

1 2	insert into dbo.USER_DATA values (3, N'テスト　プリン３', 2000, 12, 22, '2', N'テストトリガー', 0)

3) 2)実行後のUSER_DATAテーブル、USER_DETAILテーブルの内容は以下の通りで、トリガーの影響でUSER_DETAILテーブルにデータ追加されたことが確認できる。

select * from dbo.USER_DATA

1	select * from dbo.USER_DATA

select * from dbo.USER_DETAIL

1	select * from dbo.USER_DETAIL

4) USER_DATAテーブルからデータを1件削除する。

delete from dbo.USER_DATA where id = 3

1	delete from dbo.USER_DATA where id = 3

5) 4)実行後のUSER_DATAテーブル、USER_DETAILテーブルの内容は以下の通りで、トリガーの影響でUSER_DETAILテーブルからデータ削除されたことが確認できる。

select * from dbo.USER_DATA

1	select * from dbo.USER_DATA

select * from dbo.USER_DETAIL

1	select * from dbo.USER_DETAIL

6) USER_DATAテーブルのデータを1件更新する。

update dbo.USER_DATA set name = N'テスト　プリン２　更新' where id = 2

1	update dbo.USER_DATA set name = N'テスト　プリン２　更新' where id = 2

7) 6)実行後のUSER_DATAテーブル、USER_DETAILテーブルの内容は以下の通りで、トリガーの影響でUSER_DETAILテーブルのデータが更新(削除後に追加)されたことが確認できる。

select * from dbo.USER_DATA

1	select * from dbo.USER_DATA

select * from dbo.USER_DETAIL

1	select * from dbo.USER_DETAIL

トリガーの削除

トリガーの削除は、DROP TRIGGER文で行える。

DROP TRIGGER dbo.MOD_USER_DETAIL

1	DROP TRIGGER dbo.MOD_USER_DETAIL

さらに、指定したトリガーが削除されたかどうかは、以下のSQLを実行することで確認できる。

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')

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')

試しに、トリガーが削除された状態で、USER_DATAテーブルにデータを1件追加する。

insert into dbo.USER_DATA 
values (3, N'テスト　プリン３', 2000, 12, 22, '2', N'テストトリガー', 0)

1 2	insert into dbo.USER_DATA values (3, N'テスト　プリン３', 2000, 12, 22, '2', N'テストトリガー', 0)

実行後のUSER_DATAテーブル、USER_DETAILテーブルの内容は以下の通りで、トリガーが無いのでUSER_DETAILテーブルが更新されないことが確認できる。

select * from dbo.USER_DATA

1	select * from dbo.USER_DATA

select * from dbo.USER_DETAIL

1	select * from dbo.USER_DETAIL

要点まとめ

あるテーブルに追加(INSERT)／更新(UPDATE)／削除(DELETE)が行われたタイミングで、別のテーブルにデータ操作(追加／更新／削除)を行える機能をトリガーという。
Azure上のSQL Database上で、トリガーを作成できる。

前提条件

やってみたこと

USER_DETAILテーブルの追加

トリガーの作成

トリガーによるテーブル更新確認

トリガーの削除

要点まとめ

Spring BootのDBアクセス処理を手動で定義しMyBatisを利用してみた

Azure Potal上でSQLデータベースを作成してみた

Azure Potal上でCosmos DBデータベースを作成してみた

Azure FunctionsからSQL Databaseにアクセスしてみた

Azure App ServiceからCosmos DBデータベースにアクセスしてみた