はじめに
ビジネスルールを熟知したSQL Server開発者がトリガや制約を用いてルールを実装しようとしたが、どういうわけか結局は無効なデータがデータベースに紛れ込み、ビジネスに深刻な結果をもたらす羽目になった――そんな事態を目にしたことはないでしょうか。長年にわたって、私はそのような状況を幾度となく目撃してきました。SQL Serverにはデータの整合性を保証する組み込みツール（参照整合性制約など）があるにもかかわらず、経験の浅い開発者は、それを利用せずに一から自分の力でやり遂げようとします。制約を適切に使用するのではなく、独自のビジネスルール実装ソリューションを開発しようとするのです。
そこで、一見隙のないトリガや制約をすり抜けて無効なデータがデータベースに紛れ込むプロセスを理解することが極めて重要となります。
本稿では、ごく単純なビジネスルールをよく知られた複数の方法で実装し、それらのアプローチに含まれる「抜け穴」を具体的に示します。また、スナップショット分離（snapshot isolation）が、ビジネスルールの実装ミスを誘う新たなきっかけになることも示します。
サンプルテーブルとビジネスルール
「報告先は契約社員（Contractor）であってはならない」というビジネスルールを実装する必要があるものと仮定します。このビジネスルールで使用するDDLは次のとおりです。
create table employee(employee_id int not null primary key, first_name Varchar(10), last_name Varchar(10), manager_id int, status Varchar(10) check(status in('Employee', 'Contractor')))
以下はサンプルデータです。
insert into employee(employee_id, first_name, last_name, manager_id, status) values(1, 'Jane', 'Wilson', null, 'Employee') insert into employee(employee_id, first_name, last_name, manager_id, status) values(2, 'Sue', 'Smith', 1, 'Contractor') insert into employee(employee_id, first_name, last_name, manager_id, status) values(3, 'Sam', 'Brown', 1, 'Employee') insert into employee(employee_id, first_name, last_name, manager_id, status) values(4, 'Jill', 'Larsen', 3, 'Employee')
トリガが起動しないことがある
トリガは非常に強力で柔軟なため、ビジネスルールの実装を求められた開発者は、多くの場合、ためらわずトリガを利用します。しかし、トリガには次の2つの問題があります。
- トリガの作成時に既存のデータは検証されない。
- トリガが起動しないことがある。
create trigger manager_must_be_employee on employee after insert, update -- this trigger does not handle deletes, just to keep things simple -- a complete solution should prevent -- from deleting of a manager with employee as declare @contractor_has_employees int, @manager_is_contractor int if update(status) begin select @manager_is_contractor = count(*) from inserted, employee where employee.status = 'Contractor' and employee.employee_id = inserted.manager_id if @manager_is_contractor > 0 begin raiserror('Cannot insert anyone reporting to a Contractor', 16, 1) rollback tran return end select @contractor_has_employees = count(*) from inserted, employee where inserted.status = 'Contractor' and inserted.employee_id = employee.manager_id if @contractor_has_employees > 0 begin raiserror('Cannot set manager''s status to Contractor', 16, 1) rollback tran return end end
この簡単なトリガは、あらゆる事態に対応するものではありませんが、これで一部のエラーは回避されます。
update employee set status = 'Contractor' where employee_id = 3 Server: Msg 50000, Level 16, State 1,Procedure manager_must_be_employee, Line 32 Cannot set manager's status to Contractor
Procedure manager_must_be_employee, Line 32
Cannot set manager's status to Contractor
Jill Larsenの報告先はSam Brownなので、Samを契約社員にすることはできません。このトリガが起動すれば、適切なエラーが引き起こされ、ロールバックが行われます。しかし、それも「トリガが起動すれば」の話です。ここで、サーバー側で「ネストされたトリガ」がオンに設定されていて、社員の状態が別のトリガによって変更される可能性がある場合を考えてみましょう。この場合も、manager_must_be_employeeトリガはデータを保護します。
create table contract(employee_id int, amount float) go create trigger only_contractors_have_contracts on contract after insert as update employee set status='Contractor' from employee, inserted where inserted.employee_id = employee.employee_id go insert into contract values(3, 199.00) go Server: Msg 50000, Level 16, State 1,
このアプリケーションは完全にテストされた上で配備が完了しており、「employee」テーブルには無効なデータが存在しないものとします。しかし、もしデータベース管理者が「ネストされたトリガ」をオフにした場合は、同じ挿入ステートメントinsert into contract values(3, 199.00)によってトリガonly_contractors_have_contractsが起動され、それによって「employee」テーブルが変更されることになります。しかし、「ネストされたトリガ」がオフに設定されているので、manager_must_be_employeeトリガは起動せず、この挿入ステートメントは成功します。
次に、「employee」テーブルに無効なデータが存在し、そのことに気づいていないものと仮定します。Jill Larsenの報告先はSam Brownなので、Samが契約社員であることはあり得ません。しかし、Samの状態は次のようになっています。
select * from employee employee_id first_name last_name manager_id status ----------- ---------- ---------- ----------- ---------- 1 Jane Wilson NULL Employee 2 Sue Smith 1 Contractor 3 Sam Brown 1 Contractor 4 Jill Larsen 3 Employee (4 row(s) affected)
これまで見てきたように、トリガは起動しないことがあります。「ネストされたトリガ」とrecursive_triggersの設定をテスト環境と本番環境で一致させるようにしてください。
