Re: Update trigger, detecting missing columns

> I think, it can be done with two triggers and packaged variable (
> similar to mutating table workaround).
>
> SQL> CREATE TABLE ZZ (A NUMBER PRIMARY KEY, B NUMBER, C NUMBER);
>
> Table created.
>
> SQL> INSERT INTO ZZ VALUES(2,1000,0);
>
> 1 row created.
>
> SQL> CREATE OR REPLACE PACKAGE b
> 2 IS
> 3 upd_flag PLS_INTEGER := 0;
> 4 END;
> 5 /
>
> Package created.
>
> SQL> CREATE OR REPLACE TRIGGER trg_before_row
> 2 BEFORE UPDATE ON zz
> 3 for EACH ROW
> 4 BEGIN
> 5 IF b.upd_flag = 0 THEN
> 6 :NEW.b := 0;
> 7 END IF;
> 8 b.upd_flag := 0;
> 9 END;
> 10 /
>
> Trigger created.
>
> SQL> CREATE OR REPLACE TRIGGER trg_before_stmt
> 2 BEFORE UPDATE OF B ON zz
> 3 BEGIN
> 4 b.upd_flag := 1;
> 5 END;
> 6 /
>
> Trigger created.
>
> SQL> UPDATE ZZ SET C=1, B=1000 WHERE A=2;
>
> 1 row updated.
>
> SQL> SELECT A,B,C FROM ZZ WHERE A=2;
>
> A B C
> ---------- ---------- ----------
> 2 1000 1
>
> SQL> UPDATE ZZ SET C=1 WHERE A=2;
>
> 1 row updated.
>
> SQL> SELECT A,B,C FROM ZZ WHERE A=2;
>
> A B C
> ---------- ---------- ----------
> 2 0 1
>
> SQL>
>
>
> Best regards
>
> Maxim

Excellent, thank you very much! Received on Fri Dec 23 2005 - 08:49:44 CST