Re: Update trigger, detecting missing columns

From: Maxim Demenko <mdemenko_at_arcor.de>
Date: Thu, 22 Dec 2005 12:06:51 +0100
Message-ID: <43aa8950$0$16521$9b4e6d93@newsread4.arcor-online.net>

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 Received on Thu Dec 22 2005 - 05:06:51 CST