削除されたデータが本当に意図したものなのかどうかを確認できるSQL文を作成したい

Question

個人情報テーブルがあります。
プロシージャを実行した結果、個人情報テーブルから個人情報削除対象テーブルの削除フラグが「0」のデータが削除されました。
この、「個人情報テーブルから削除されたデータが、本当に個人情報削除対象テーブルの削除フラグ0のデータだったのか」を確認できるSQL文を作成できたらと考えましたが、なかなか妙案が浮かびません。

select * from 個人情報テーブル
　where exists(
    select * from 個人情報削除対象テーブル
　　　　　where 個人情報テーブル.ID = 個人情報削除対象テーブル.ID 
         AND 個人情報削除対象テーブル.削除フラグ = 0)

このSQL文は

プロシージャを実行した結果個人情報削除対象テーブルの削除フラグが「0」だったデータは個人情報テーブルからはいなくなりましたよ～＝データが何も出力されない

という結果を狙ったものなのですがいかがでしょうか。
（頓珍漢なSQL文の可能性があります、、、）

SQL文に対してであったり、他にも良い案がございましたらどなたかご教授いただければ幸いです。
どうぞよろしくお願いいたします。

Answer 1

この、「個人情報テーブルから削除されたデータが、本当に個人情報削除対象テーブルの削除フラグ0のデータだったのか」を確認できるSQL文を作成できたらと考えましたが、なかなか妙案が浮かびません。

トリガーで、削除したデータを保存するテーブルに出力すれば良いかと思います。

ですが、

「個人情報テーブルから削除されたデータが、本当に個人情報削除対象テーブルの削除フラグ0のデータだったのか」

については、トリガーでの負荷を考えると、十分にテストしておけば済む話だと思いますけど。

Answer 2

原理的に不可能かと思います。

削除したデータをSQL文でマッチさせることはできませんので、余分に削除していたのを検出する方法はないかと思います。

Comments

[ypk]

ありがとうございます！削除したデータをSQL文でマッチさせることは確かにできませんね、、、

それでは、

select * from 個人情報テーブル
　where exists(
select * from 個人情報削除対象テーブル
　　　　　where 個人情報テーブル.ID = 個人情報削除対象テーブル.ID
AND 個人情報削除対象テーブル.削除フラグ = 0)

というSQL文をファンクション実行後に実行し

個人情報削除対象テーブルにある削除フラグが0のデータが個人情報テーブルに存在しない＝個人情報削除対象テーブルの削除フラグ0のデータはファンクション実行後に個人情報テーブルから削除された

と判断することはありなのでしょうか。
お手数をおかけしますがどうぞよろしくお願いいたします。

Answer 3

後から確認が出来ることを要件とするのであれば、
レコードを削除するのではなく、データを適当な文字列や空文字で更新してしまう
と言うのはありかもしれません。
（誤削除を検知したとしても、復旧は出来ませんが。）

ただ、これはアプリケーションの機能として組み込むべき機能と言うよりは、開発時の十分なテストケースによって保障されるべきかなと思います。

ここでアプリケーションの機能として誤削除の検知を組み込むのであれば、
削除フラグの登録が正しく出来ているかどうかというところも組み込まないと片手落ちになるでしょうし（削除フラグの登録に限らず、削除に関係する全フェーズ）、
そうすると「全部のデータ変更履歴を残しておく」みたいな当初の目的（個人情報の適切な削除）からかけ離れた実装しか選択肢が残らない気がします。

Comments

[ypk]

なるほど、、やはり削除されてしまったものを確認するということは難しいのですね、、、ありがとうございます

それでは、削除されたデータの中身までは確認できなくても、正しく削除フラグが0だったデータが削除されていることを確認するために

select * from 個人情報テーブル
　where exists(
select * from 個人情報削除対象テーブル
　　　　　where 個人情報テーブル.ID = 個人情報削除対象テーブル.ID
AND 個人情報削除対象テーブル.削除フラグ = 0)

というSQL文をファンクション実行後に実行し

個人情報削除対象テーブルにある削除フラグが0のデータが個人情報テーブルに存在しない＝個人情報削除対象テーブルの削除フラグ0のデータはファンクション実行後に個人情報テーブルから削除された

と判断することはありなのでしょうか。
お手数をおかけしますがどうぞよろしくお願いいたします。

[tanat]

出来る出来ないで言えば出来るでしょうが、
（おそらくは）削除する時と原理的に同じ条件で検索することに何か意味があるかと言えば、ほぼ意味はないんじゃないかなと思います。

例えばもっとシンプルな例で
DELTE FROM test WHERE delete_flg = 0;
SELECT count(*) FROM test WHERE delete_flg = 0;
として、2行目のSELECT文で削除フラグが残っていないことを確認しないといけない（結果が異なる可能性がある）としたら、
それはRDBMSやハードウェアがぶっ壊れているか、DELETE文にタイプミスがあったりのバグがある時や、トランザクションを適切にかけることが出来ておらず、DELETE後にデータ登録されちゃったようなケースですよね。
RDBMSやハードウェアがぶっ壊れている事をアプリケーションの責任で検知するのは無理なので、やる意味はありません。（ぶっ壊れているのであれば、そもそもSELECT文の結果が正しいかどうかすら保証されないわけで。）

プログラムのミスは、適切なテストケースによってチェックされるべきであって、実行中にベリファイするのは保守性が悪化するだけの悪手だと思います。

また、誤って過剰に削除してしまった場合には対応出来ませんがそれは良いんでしょうか？

等々の論点があるのでまずは、
最終的なゴール（具体的に何を目的として検知を行いたいのか、検知できたとしてその結果はどのように使われるのか）を明確にしないことには実装について検討する判断基準が得られず、最終的には好みと開発者の満足度の問題になってしまいます。

例えばRDBMSの信頼性が極端に低く、処理が途中で止まることがよくあり、途中で止まってもトランザクションが働かずロールバックされることが保証されていない　みたいな特殊な事情があるのであれば、
実行後にその結果を確認し、不足があったらリトライする　というのは必要な実装だとは思います。

[ypk]

詳しく説明してくださりありがとうございました。
削除フラグに基づいてデータを削除してくれるファンクションを作成し、そのファンクションの実行結果を確かめたかったのですが、あまり意味がない話なのかもしれませんね、、、、

[tanat]

そうですね。
「何のために」ファンクションの実行結果を確認したいかを明確にされると適切な方法が見えてくるかと思います