Re: Update trigger, detecting missing columns

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Wed, 21 Dec 2005 19:50:48 +0100
Message-ID: <43a9a408$0$14563$626a14ce@news.free.fr>

"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 Received on Wed Dec 21 2005 - 12:50:48 CST