特定のカラムが更新されたときのみ、そのレコードを別テーブルへ格納するト

質問

特定のカラムが更新されたときのみ、そのレコードを別テーブルへ格納するトリガの作成方法を教えてください・

トリガ名：TEST_UPDATE
テーブル名：MV_ORACLE_MV_TEST
カラム名：ORAPRIME
ORACHANGE
ORANAME
CHANGENAME

対象カラム：CHANGENAME
格納先テーブル：TRITEST_ORACLE_MV_TEST

作成トリガ：
CREATE OR REPLACE TRIGGER TEST_UPDATE
after update of CHANGENAME on MV_ORACLE_MV_TEST
FOR EACH ROW
begin
INSERT INTO TRITEST_ORACLE_MV_TEST
SELECT MV_ORACLE_MV_TEST.ORAPRIME ,
MV_ORACLE_MV_TEST.ORACHANGe ,
MV_ORACLE_MV_TEST.ORANAME ,
MV_ORACLE_MV_TEST.CHANGENAME
FROM
MV_ORACLE_MV_TEST
where
MV_ORACLE_MV_TEST.CHANGENAME = :new.CHANGENAME
-- commit;

commit;
end
/

テーブルMV_ORACLE_MV_TESTのうち、CHANGENAMEカラムが変更（更新）されたレコードのみを、
TRITEST_ORACLE_MV_TESTテーブルに格納したいと思っております。
この時、他のカラムも一緒に格納することが必要でして、
（もしカラムに変更があった場合、そのカラムも変更する。変更されていない場合は元の値を格納する）
そのトリガを上記のように書いたのですが、
コンパイルエラーが発生しました。
どの部分が誤っているのか、ご教授願えませんでしょうか

回答No.7

yamada_g · ベストアンサー

・・・ご自身で少しは考えていらっしゃいますか？
やりたいことはそういうことなのだろうと思っていましたが敢えて書きませんでした。

CHANGENAMEが変更されたら、というIF文を書けばいいだけなのではないですか？
if :old.CHANGENAME != :new.CHANGENAME THEN
--insert
end if;

CHANGENAMEにnullが入る場合は上記の条件では足りないと思いますが、
そこはご自身で書いてください。

お礼

ありがとうございました。
if文は最初考えていたのですが、
指定する場所を間違えていたために失敗して諦めていました。

nullに関しては、テーブル側でnot null 制約のあるカラムに指定することによって対処しました

回答No.6

yamada_g

No.1、No.4です。

なぜそうしようと思うのかが自分にはよく分かりません。
MV_ORACLE_MV_TESTは参照できないということも言っているのですが・・・
No.2さんが
>selectするんじゃなく、:new.xxxxxをinsertの値にしてください。
と回答してくれていますよね？
素直に
insert into　TRITEST_ORACLE_MV_TEST
values
(
:new.ORAPRIME ,
:new.ORACHANGe ,
:new.ORANAME ,
:new.CHANGENAME
)
とすればいいのでは？
マニュアルは読みましたか？

お礼

ありがとうございます。
上記の形で先程実行したのですが、

その際、マテリアライズド・ビューへの更新が複数発生したときに
CHANGENAMEを変更していないレコードも抜き出してしまいましたため、
selectで別途絞ることが必要なのかと思ってselectが必要なのかと書きました。
言葉足らずで申し訳ございません。

下記の条件で不要なものが出てしまいました。
・マテリアライズド・ビューの更新の時、CHANGENAMEを変更したものとCHANGENAME以外のカラムを変更したものが混在して変更される
・別テーブルへの書き込みがマテリアライズド・ビューへのCHANGENAMEカラムの変更があった場合をトリガとしている
・そのため、トリガの起動条件（CHANGENAMEカラムの変更があるレコードが発生）を満たしているときに、CHANGENAMEカラム以外の変更がされたレコードがあった場合、そのレコードも一緒に書き出されてしまう

と言う事象です。

※
(1)MVIEWの更新で200件更新が実行される
(2)別テーブル抜き出しの対象となる、CHANGENAMEカラムが変更されたものはそのうちの40件
(3)MVIEWでCHANGENAMEカラムに変更があることをトリガとし、トリガはその時に更新されたすべてのレコードを別テーブルに抜き出しているため、別テーブルへは200件が抜き出されてしまう

→目的は、このようなケースで(2)に該当する40件を抜き出したい

回答No.5

SaKaKashi

INSERTする値が、UPDATEトリガーの対象の表なのですからSELECTで値を求める必要はないと思いますけどね。
全て、:new.項目名でいいんじゃないですか。

CREATE OR REPLACE TRIGGER Log_salary_increase
AFTER UPDATE ON Emp_tab
FOR EACH ROW
BEGIN
INSERT INTO Emp_log (Emp_id, Log_date, New_salary, Action)
VALUES (:new.Empno, SYSDATE, :new.SAL, 'NEW SAL');
END;

お礼

ありがとうございます。

ただ、上記の方法ですと、

(1)MVIEWの更新で200件更新が実行される
(2)別テーブル抜き出しの対象となる、CHANGENAMEカラムが変更されたものはそのうちの40件
(3)MVIEWでCHANGENAMEカラムに変更があることをトリガとし、トリガはその時に更新されたすべてのレコードを別テーブルに抜き出しているため、別テーブルへは200件が抜き出されてしまう

という事象が発生してしまったので、どこかでselect条件が必要なのかなと考え、
select文を条件指定に入れていました。

今回、MVIEWに反映されたもののうち、update of 句で指定したカラムが変更されたレコードのみを抽出したいので、その時に同時に出てくる別レコード（update of句以外の箇所が変更されたレコード）を抽出しないという条件がネックになっています。

回答No.4

yamada_g

No.1です。
>本件は元テーブル側に原因がありました。
とありますが、コンパイル自体が出来ないのはセミコロンが抜けているからなのではないですか？
実際は記載されたコードと違ったということなのでしょうか。

>トリガのupdate条件は、
>マテリアライズド・ビューの更新を見ることはできないのでしょうか？
高速リフレッシュならばupdate文が実行されるはずですので検知できると思います。
完全リフレッシュだとdelete（もしくはtruncate)→insertとなるはずなので、検知できないのではないでしょうか。

また、
>もしかして、トリガの場合、insert into ～ select ～
>のテーブルコピー文法は使用できないのですか？
トリガーでは変更表（今回であればMV_ORACLE_MV_TEST）を参照することはできません。
ですので、No.2さんのご指摘にあるようにINSERT文を修正してください。

http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/appdev.102/B19248-02/adfns_triggers.htm

お礼

セミコロンについては申し訳ございません。
このページに貼るときにコピーが漏れていました
（where句の最終位置のセミコロンが抜けていました）

MVIEWのリフレッシュは高速リフレッシュになっています

また、非常に申し訳ないのですが、
insert文の変更イメージが良く沸かないのですが、

insert into　TRITEST_ORACLE_MV_TEST
values
(
aaa,
bbb,
ccc,
ddd
)
で書くときに、予めそれぞれの値を文頭でdeclareすることが必要ということでしょうか？

つまり、
declare
aaa VARCHAR2(45) select ORAPRIME into ORAPRIME from MV_ORACLE_MV_TEST where MV_ORACLE_MV_TEST.CHANGENAME = :new.CHANGENAME;
bbb ～～～（以下、aaaに同じ）

をbeginの前に追加する方法と言う意味です

回答No.3

SaKaKashi

コンパイルエラーの内容がわかりませんので、これ以上のコメントは無理かと。
マニュアルの一部ですが、

トリガー本体で使用可能なSQL文
トリガー本体には、DML SQL 文を含めることができます。 また、SELECT 文を含めることはで
きますが、SELECT... INTO... 文またはカーソル定義内のSELECT 文を指定する必要があります。

お礼

すいません。
コンパイルエラーについては、元テーブル（MVIEW）の命名エラーでしあt。
ご迷惑をおかけいたしました。

>SELECT... INTO... 文またはカーソル定義内のSELECT 文を指定する必要があります。
というのは、declareが必要ということでしょうか？

あるいは、下記のような書き方なのでしょうか？
INSERT INTO TRITEST_ORACLE_MV_TEST
SELECT (
　　　　 select ORAPRIME into oraprime from MV_ORACLE_MV_TEST,
select ORACHANGE into ORACHANGE from MV_ORACLE_MV_TEST ,
select ORANAME into ORANAME from MV_ORACLE_MV_TEST,
select CHANGENAME into CHANGENAME from MV_ORACLE_MV_TEST
)
FROM MV_ORACLE_MV_TEST
where MV_ORACLE_MV_TEST.CHANGENAME = :new.CHANGENAME;

回答No.2

SaKaKashi

このタイプのトリガー内にはcommitは書けません。
selectするんじゃなく、:new.xxxxxをinsertの値にしてください。

お礼

ありがとうございます。
commitは作成途中の残骸でした。
（こちらを削除しました）

もしかして、トリガの場合、insert into ～ select ～
のテーブルコピー文法は使用できないのですか？

回答No.1

yamada_g

>コンパイルエラーが発生しました。
エラーメッセージは読みましたか？どの行でエラーになっているかも出力されていると思います。
読んでも分からないのであれば、少しずつコードを追加していく等をして、
どこでエラーになっているのかを自分で見つけてください。

エラーの原因はただの文法ミスです。
余計なお世話ですが、これくらいは自分で見つけられるようにならないとこれからも大変だと思いますよ。

補足

すいません。
本件は元テーブル側に原因がありました。
お騒がせいたしました。

また、もう一つご教授願いたいのですが、
トリガのupdate条件は、
マテリアライズド・ビューの更新を見ることはできないのでしょうか？

本文を引用しますと、
after update of CHANGENAME on MV_ORACLE_MV_TEST
の「MV_ORACLE_MV_TEST」
がマテリアライズド・ビューであり、
元テーブルの変更→マテリアライズド・ビューの更新の発生した際に、
マテリアライズド・ビュー内の当該レコードを別テーブルに保管しようとしたのですが、
更新ができませんでした