not equal (!=) in trigger [message #307372] |
Tue, 18 March 2008 11:42  |
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 #307377 is a reply to message #307372] |
Tue, 18 March 2008 12:12   |
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 #307395 is a reply to message #307372] |
Tue, 18 March 2008 15:06  |
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
|
|
|