SQLServerのトリガーを作ってみた

皆さん、こんにちは！

Package Service Divisionに所属している新卒エンジニアのトゥアンです。

今回は開発で初めて作った「SQLServerのトリガー」について紹介したいと思います！

1. トリガとは

「トリガー」とはテーブルに対するデータ操作文(InsertやUpdate, Delete)をきっかけに自動的に実行される機能のことです。

今まで作ったことなく、商品を登録する際に関連するテーブルを一緒に更新するSQL用意して対応していました。

2. 実際のSQLの例

例えば、商品を登録する際に自動的に在庫を１として登録したいという要求があるとします。その場合、新たな商品が作成されたことをきっかけに在庫を登録するトリガーを定義することで上記の要求を実現することができます。

それでは続いて、実際のSQLを作成してみましょう！

-- 商品テーブル
CREATE TABLE tbl_Product
(
	product_id NVARCHAR(20) NOT NULL PRIMARY KEY,
	product_name  NVARCHAR(20),
	display_price DECIMAL(18,0)
)
-- 商品在庫テーブル
CREATE TABLE tbl_ProductStock
(
	product_id NVARCHAR(20) NOT NULL PRIMARY KEY,
	stock INT
)

トリガーの作成SQLです。

-- INSERTの商品在庫登録用
CREATE TRIGGER [dbo].[AddProductStockTr]
ON [dbo].[tbl_Product]
AFTER INSERT
AS
BEGIN
	-- 商品在庫登録
	INSERT tbl_ProductStock
	(
		product_id,
		stock
	)
　--在庫を１として割り振る
	SELECT	product_id,
			　　1
	FROM	inserted
	WHERE	NOT EXISTS
			(
				SELECT *
				FROM tbl_ProductStock
				WHERE tbl_ProductStock.product_id = inserted.product_id
			)
END

3. トリガーテスト

まずは、トリガーが仕様通りの挙動となっていることを確かめましょう！

tbl_Productテーブルに行を追加すると以下のようになります。

INSERT INTO tbl_Product
	(product_id, product_name, display_price)
VALUES
	('0001', N'テレビ', '26000'),
	('0002', N'パソコン', '52000'),
	('0003', N'冷蔵庫', '65000')

商品テーブルにデータが挿入されます。

商品在庫テーブルが自動で１として登録されています。

仕様を満たした挙動となっていることが、なんとなく理解できるのではないでしょうか。データベース側だけで自動的なデータ操作が可能となり、便利そうな気がしますね。

トリガーで実行したSQLでエラーが発生するとトランザクションの状況によってはロールバックが発生し、メインテーブルの変更も無効になったりするので注意が必要です。

4. おわりに

ここまで、私が初めて作成したSQLServerのトリガーについて紹介してきました。

一見便利そうなトリガーですが、使う際に意識すべきことは「データベースにビジネスロジック持たせる」という点です。アプリ側でロジックを変更してもデータベース側も修正する必要が出てきます。ただ、保守性を考慮した上で設計をすれば、運用中に大きな問題が出ないでしょう。