CodeZine(コードジン)

特集ページ一覧

データベースに無効なデータが紛れ込まないようにするコツ

ありがちなビジネスルールの実装ミスを防ぐ

  • ブックマーク
  • LINEで送る
  • このエントリーをはてなブックマークに追加
2006/09/15 00:00

ビジネスルールを実装するソリューションで、データベースに無効なデータが紛れ込むことがあります。ありふれたアプローチのどこに抜け穴があるのか具体的に調べてみましょう。

目次

はじめに

 ビジネスルールを熟知した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つの問題があります。

  • トリガの作成時に既存のデータは検証されない。
  • 誰かがトリガを削除してから無効なデータを追加し、その後、トリガを作り直したとすると、知らない間に不正なデータが紛れ込むことになります。
  • トリガが起動しないことがある。
  • サーバーレベルの「ネストされたトリガ」とデータベースレベルのrecursive_triggersにより、トリガの起動が阻止されることがあります。次の簡単なトリガの例は、この問題の完全なソリューションではありませんが(コード内のコメントを参照)、重要なポイントを示しています。
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

 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,
 Procedure manager_must_be_employee, Line 34
Cannot set manager's status to Contractor

 このアプリケーションは完全にテストされた上で配備が完了しており、「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の設定をテスト環境と本番環境で一致させるようにしてください。


  • ブックマーク
  • LINEで送る
  • このエントリーをはてなブックマークに追加

あなたにオススメ

著者プロフィール

  • Alexander Kuznetsov(Alexander Kuznetsov)

    データベースの設計、開発、トラブルシューティング、管理に10年以上携わる。1994年以来Sybaseの仕事を担当し、1998年以降はMS SQL Serverにも携わっている。2001年以降は主要なすべてのRBDMS(DB2、Oracle、SQL Server)にかかわっている。MCP 70-229...

  • japan.internet.com(ジャパンインターネットコム)

    japan.internet.com は、1999年9月にオープンした、日本初のネットビジネス専門ニュースサイト。月間2億以上のページビューを誇る米国 Jupitermedia Corporation (Nasdaq: JUPM) のニュースサイト internet.com や EarthWeb.c...

バックナンバー

連載:japan.internet.com翻訳記事

もっと読む

All contents copyright © 2005-2021 Shoeisha Co., Ltd. All rights reserved. ver.1.5