Michel Cadot wrote:
> "DA Morgan" <damorgan_at_psoug.org> a écrit dans le message de news: 1135185379.283905_at_jetspin.drizzle.com...
> | 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)
>
> The issue is the following one:
> - if B is given in the set clause then set B to this new value
> - if B is not given is the set clause then clear B (for instance).
>
> Now the example:
> create table zz (a number primary key, b number, c number);
> insert into zz values(2,1000,0);
>
> update zz set c=1, b=1000 where a=2;
> select a,b,c from zz where a=2;
> --> there row must be 2,1000,1
>
> update zz set c=1 where a=2;
> select a,b,c from zz where a=2;
> --> there row must be 2,0,1
>
> I don't see how to write a trigger that can handle that as in both cases :old.B=:new.B=1000.
>
> Regards
> Michel Cadot
You are correct. I see two possible workarounds.
- Use Fine Grained Auditing which will allow you to then use:
SELECT sys_context('USERENV', 'CURRENT_SQL')
INTO x
FROM dual;
and examine the SQL statement ... ugly.
2. Create a stored procedure accepting as parameters A, B, and C
and disallow direct table updates ... not exactly a thing of beauty.
I know which one I'd choose.
--
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)
Received on Wed Dec 21 2005 - 13:14:44 CST
