Re: Update trigger, detecting missing columns

"DA Morgan" <damorgan_at_psoug.org> a écrit dans le message de news: 1135123383.641521_at_jetspin.drizzle.com...
|
| Then what's wrong with:
|
| IF :NEW.bonus IS NULL THEN
|
| or
|
| IF :NEW.bonus IS NULL and :OLD.bonus IS NOT NULL THEN
|
| I still don't see what it is you are trying to do that you don't
| already have the tools to handle.
| --
| Daniel A. Morgan
| http://www.psoug.org
| damorgan_at_x.washington.edu
| (replace x with u to respond)

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 trigger zz_bu before update on zz for each row   2 begin
  3 dbms_output.put_line('New B='||:new.b);   4 end;
  5 /

Trigger created.

SQL> update zz set c=1 where a=2;
New B=1000

1 row updated.

When you don't set B in update, :new.B is set to its previous value. The question is: how to know if B is not set or if B is set to 1000 in the update?

Maybe to explicitly set it to NULL in the update:

SQL> update zz set c=1, b=null where a=2; New B=

1 row updated.

Regards
Michel Cadot Received on Wed Dec 21 2005 - 00:34:01 CST