Home » SQL & PL/SQL » SQL & PL/SQL » not equal (!=) in trigger (Oracle 10g)
not equal (!=) in trigger [message #307372] Tue, 18 March 2008 11:42 Go to next message
bluecake
Messages: 2
Registered: March 2008
Junior Member
I created an instead of trigger against a view. When column orgcodes is updated, the underline table should be updated.

CODES that don't work:

create or replace trigger tgr_user_update
instead of update on vw_mlearning_user
for each row
begin
IF UPDATING ('orgcodes') AND :OLD.orgcodes != :NEW.orgcodes THEN
UPDATE edir_user SET orgcodes = :NEW.orgcodes, lastupdateddate = sysdate
WHERE guid = :OLD.pk_guid;
END IF;
end;
/

But when I changed it to:

IF :OLD.orgcodes = :NEW.orgcodes THEN
DBMS_OUTPUT.PUT_LINE('out');
ELSE
UPDATE edir_user SET orgcodes = :NEW.orgcodes, lastupdateddate = sysdate
WHERE guid = :OLD.pk_guid;
END IF;

Why does != or <> not work? What is the best way to write this trigger?

Thanks.
Re: not equal (!=) in trigger [message #307374 is a reply to message #307372] Tue, 18 March 2008 11:50 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
>CODES that don't work
Above is likely true, but 100% worthless to get you a meaningful answer.

My car does not work.
Tell me how to make it go.

We have no idea what it is doing?
We don't know what you expect it to do.

You're On Your Own (YOYO)!
Re: not equal (!=) in trigger [message #307377 is a reply to message #307372] Tue, 18 March 2008 12:12 Go to previous messageGo to next message
Tafer
Messages: 64
Registered: July 2005
Location: Here!
Member
If the "orgcodes" column contains NULL values, <> is not going to work.

Try with:

create or replace trigger tgr_user_update
  instead of update on vw_mlearning_user
  for each row
begin

   IF     UPDATING ('orgcodes') 
      AND nvl(:OLD.orgcodes,0) != nvl(:NEW.orgcodes,0)
   THEN
      UPDATE edir_user 
         SET orgcodes = :NEW.orgcodes, lastupdateddate = sysdate
       WHERE guid = :OLD.pk_guid;
   END IF;

end;
Re: not equal (!=) in trigger [message #307380 is a reply to message #307377] Tue, 18 March 2008 12:24 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8633
Registered: November 2002
Location: California, USA
Senior Member
You might also need to do the same with the where clause, depending on what results you want:

WHERE nvl(guid,0) = nvl(:OLD.pk_guid,0);
Re: not equal (!=) in trigger [message #307388 is a reply to message #307377] Tue, 18 March 2008 13:52 Go to previous messageGo to next message
bluecake
Messages: 2
Registered: March 2008
Junior Member
Tafer,

You are right. != does not handle NULL properly. I add NVL and it works.

Thank you for your help.

Re: not equal (!=) in trigger [message #307395 is a reply to message #307372] Tue, 18 March 2008 15:06 Go to previous message
Tafer
Messages: 64
Registered: July 2005
Location: Here!
Member
Good to hear it helped you.

In any case, Barbara's comment is also very important. And have in mind that we used nvl(value,0) assuming that 0 is going to work as an "impossible value".

In other words, if your business logic permits the value 0 for the column "orgcodes", you will have to think in any other value that it's impossible for such column. Let me illustrate this with an example:

Let's say you have "orgcodes" :OLD value is 0 and the :NEW value is NULL... when Oracle evaluates nvl(:OLD.orgcodes,0) != nvl(:NEW.orgcodes,0) it will find that both are the same, and the code in question will not execute.

However if you used, let's say -1, when "orgcodes" only allows positive numbers, the previous problem will never happen.

Heh, I guess that's all. Good luck!

[Updated on: Tue, 18 March 2008 15:08]

Report message to a moderator

Previous Topic: HOW TO USE :NEW AND :OLD IN EXECUTE IMMEDIATE
Next Topic: V$SQL View
Goto Forum:
  


Current Time: Tue Dec 06 10:06:24 CST 2016

Total time taken to generate the page: 0.28134 seconds