Re: Update trigger, detecting missing columns

Michel Cadot wrote:
> "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

The RETURNING Clause

conn hr/hr

var bnd1 NUMBER
var bnd2 VARCHAR2(30)
var bnd3 NUMBER

UPDATE employees
SET job_id ='SA_MAN', salary = salary + 1000, department_id = 140
WHERE last_name = 'Jones'
RETURNING salary*0.25, last_name, department_id INTO :bnd1, :bnd2, :bnd3;

print bnd1
print bnd2
print bnd3

Would be one solution. But the following statement still works for me:

IF :OLD.value IS NULL AND :NEW.value IS NULL THEN

ELSIF :OLD.value IS NULL AND :NEW.value IS NOT NULL THEN

ELSIF :OLD.value IS NOT NULL AND :NEW.value IS NULL THEN

ELSE END IF; Is there a fifth possibility?

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)