Maxim Demenko wrote:
> Michel Cadot schrieb:
>
>> "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
>>
>>
>>
>>
> 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
Nice work.
--
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)
Received on Thu Dec 22 2005 - 11:00:02 CST
