Home » SQL & PL/SQL » SQL & PL/SQL » TRIGGER
TRIGGER [message #281661] Sun, 18 November 2007 23:54 Go to next message
sbadriprasad
Messages: 14
Registered: September 2007
Junior Member
hi Experts,

i have written a trigger to notify a table changes. the trigger is
CREATE OR REPLACE TRIGGER trg_update BEFORE UPDATE OF Column1 ON Table1 FOR EACH ROW

BEGIN

INSERT INTO HistoryTable (ID, LID, FieldName, TableName, OldValue, NewValue, CreationDateTime)

VALUES (dbor.SEQ_LRQNotify.NEXTVAL, :OLD.LID, 'Column1', 'Table1', :OLD.Column1 , :NEW.Column1 , SYSDATE);

END;
then when ever the column old and new value is same also the trigger executed. to avoid this scenario i have idded a if statemnt in the trigger,
CREATE OR REPLACE TRIGGER trg_update BEFORE UPDATE OF Column1 ON Table1 FOR EACH ROW

BEGIN

IF (:OLD.Column1!= :NEW.Column1) THEN

INSERT INTO HistoryTable (ID, LID, FieldName, TableName, OldValue, NewValue, CreationDateTime)

VALUES (dbor.SEQ_LRQNotify.NEXTVAL, :OLD.LID, 'Column1', 'Table1', :OLD.Column1 , :NEW.Column1 , SYSDATE);

END IF;

END;

this the insert statement will execute only when the old and new values are not equal.
now i am having a prolem like, when ole value is null i update the column to some value the insert is not working and also from some value to null also the insert statement is not fireing (if statement fails)
so if there is any mistake in the trigger let me know.
waiting for your help.
Re: TRIGGER [message #281666 is a reply to message #281661] Mon, 19 November 2007 00:03 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
> the insert is not working
My car is not working.
Tell me how to make my car work.
Re: TRIGGER [message #281670 is a reply to message #281661] Mon, 19 November 2007 00:12 Go to previous messageGo to next message
sbadriprasad
Messages: 14
Registered: September 2007
Junior Member
i want to know whether the if condition is right.
except for one condition all other condition it should work. if old and new values are same we should not insert the value to the history table.
Re: TRIGGER [message #281679 is a reply to message #281670] Mon, 19 November 2007 00:34 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
then include those conditions in your trigger code and see what happens.post your code (formatted) if you are stuck.



regards,
Re: TRIGGER [message #281681 is a reply to message #281661] Mon, 19 November 2007 00:37 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>i want to know whether the if condition is right.
YES, NO, MAYBE.
If you do not know the answer, how will you know which answer is correct when presented to you?
Re: TRIGGER [message #281683 is a reply to message #281661] Mon, 19 November 2007 00:42 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Hi prasad ,

Actually Ypur trigger code ignores One of the basic principe of Oracle (rather RDBMS) .

NULL Never equel Or equels NULL


You need to use NVL i suggest.

Thumbs Up
Rajuvan
Re: TRIGGER [message #281686 is a reply to message #281683] Mon, 19 November 2007 00:47 Go to previous messageGo to next message
sbadriprasad
Messages: 14
Registered: September 2007
Junior Member
this is the trigger i user
CREATE OR REPLACE TRIGGER trg_update BEFORE UPDATE OF Column1 ON Table1 FOR EACH ROW

BEGIN

IF (:OLD.Column1 != :NEW.Column1) THEN

INSERT INTO HistoryTable (ID, LID, FieldName, TableName, OldValue, NewValue, CreationDateTime)

VALUES (dbor.SEQ_LRQNotify.NEXTVAL, :OLD.LID, 'Column1', 'Table1', :OLD.Column1 , :NEW.Column1 , SYSDATE);

END IF;

END;

when Column1 has NULL and i update that column to some value. the trigger is not executed.
my doubt is ,
the if condition (if NULL != 'some value') will return true or false?
Re: TRIGGER [message #281687 is a reply to message #281666] Mon, 19 November 2007 00:48 Go to previous messageGo to next message
Theracersman
Messages: 28
Registered: November 2007
Location: Indonesia
Junior Member

why,your car not work...
can i help u??
Re: TRIGGER [message #281688 is a reply to message #281687] Mon, 19 November 2007 00:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, tell us why the car is not working.

Regards
Michel

[Updated on: Mon, 19 November 2007 00:53]

Report message to a moderator

Re: TRIGGER [message #281690 is a reply to message #281688] Mon, 19 November 2007 00:57 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Rajuvan hinted in the right direction. Read his post again. Your If statement lacks an NVL.

MHE
Re: TRIGGER [message #281691 is a reply to message #281686] Mon, 19 November 2007 00:58 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
Quote:

my doubt is ,
the if condition (if NULL != 'some value') will return true or false?


i think you didn't read what

rajavu1 said :

Quote:

NULL Never equel Or equels NULL


regards,
Re: TRIGGER [message #281696 is a reply to message #281688] Mon, 19 November 2007 01:04 Go to previous messageGo to next message
Theracersman
Messages: 28
Registered: November 2007
Location: Indonesia
Junior Member

what type&brand u car have??

i have step for check u car :
step 1
check your car ignition,cable2

step2
check your material roasted.

step3
check your sparkplug,my not fire.


Re: TRIGGER [message #281698 is a reply to message #281691] Mon, 19 November 2007 01:08 Go to previous messageGo to next message
sbadriprasad
Messages: 14
Registered: September 2007
Junior Member
please give me a right if statement for these conditions. i dont know how to use NVL in IF statement. i am new to oracle.
i am struck with this. please help me.
Re: TRIGGER [message #281699 is a reply to message #281696] Mon, 19 November 2007 01:13 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Step 4: Check you have fuel.
Step 5: Check you are using a key and not (say) a pencil.
Step 6: Make sure it's your car, not someone elses in which your key is unlikely to work.
Step 7: Did the car ever work? Are your expectations too high?
Step 8: Check the engine hasn't been stolen.
Step 9: Check that 2000 mutton-birds have not started roosting under the bonnet (or "hood", for our US friends)
Step 10: Check the car has been transmuted into a chicken by a mischevious extra-terrestrial entity.
Re: TRIGGER [message #281700 is a reply to message #281661] Mon, 19 November 2007 01:13 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Check the Simplestlink you can read for NVL function .

Thumbs Up
Rajuvan

[Updated on: Mon, 19 November 2007 01:14]

Report message to a moderator

Re: TRIGGER [message #281701 is a reply to message #281698] Mon, 19 November 2007 01:13 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member

nvl(col_name,0).then first search for NVL.



regards,
Re: TRIGGER [message #281703 is a reply to message #281698] Mon, 19 November 2007 01:17 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Read the OraFAQ Forum Guide. Check out the "useful links" section. There's a direct link to the Oracle documentation. Bookmark that link, know it by heart and learn to use it. I use it on a daily basis, I really do. In that documentation you'll find the "PL/SQL User's Guide and Reference". And there you'll find this little phrase:
2 Fundamentals of the PL/SQL Language

The function NVL returns the value of its second argument if its first argument is null. In Example 2-30, if the column specified in the query is null, the function returns the value -1 to signify a non-existent employee in the output:
DECLARE
   the_manager employees.manager_id%TYPE;
   name employees.last_name%TYPE;
BEGIN
-- NULL is a valid argument to NVL. In this case, manager_id is null
-- and the NVL function returns -1.
   SELECT NVL(manager_id, -1), last_name
      INTO the_manager, name FROM employees WHERE employee_id = 100;
   DBMS_OUTPUT.PUT_LINE(name || ' is managed by employee Id: ' || the_manager);
END;
/

Now, have a look at NVL in there. It takes two parameters and returns the first one if that is not null, and it will return the second one if it is null. Have a go at it, post what you tried and we'll guide you from there. Don't forget to include code tags.

MHE
Re: TRIGGER [message #281705 is a reply to message #281701] Mon, 19 November 2007 01:19 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
How would you use nvl here? What alternative value will you use? Are you sure that value will never occur?
Better code it out:
if :new.column1 != :old.column1
or (   :new.column1 is null
   and :old.column1 is not null
   )
or (   :new.column1 is not null
   and :old.column1 is null
   )
Re: TRIGGER [message #281715 is a reply to message #281705] Mon, 19 November 2007 01:34 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Frank wrote on Mon, 19 November 2007 08:19

How would you use nvl here? What alternative value will you use? Are you sure that value will never occur?
CHR(0) is -in our setup- a package constant. The analysts are pretty confident that this particular value will never occur.
Frank wrote on Mon, 19 November 2007 08:19

Better code it out:
Sure, that's another option. But in real life, I've seen more NVL implementations like this:
NVL(:OLD.value,CHR(0)) != NVL(:NEW.VALUE,CHR(0))


But fair's fair: if you code it out, you're a 100% sure. That's something the "NVL way" can never achieve.

MHE
Re: TRIGGER [message #281728 is a reply to message #281698] Mon, 19 November 2007 02:22 Go to previous messageGo to next message
Theracersman
Messages: 28
Registered: November 2007
Location: Indonesia
Junior Member

if (condition) then
field_name := nvl(field_name,0)
else
field_name := null;
end if

regards
theracersman:D
Re: TRIGGER [message #281730 is a reply to message #281715] Mon, 19 November 2007 02:41 Go to previous messageGo to next message
sbadriprasad
Messages: 14
Registered: September 2007
Junior Member
hi,

(NVL(:OLD.Column1,CHR(0)) !=NVL(:NEW.Column1,CHR(0)))
it worked exactely for varchar type.
when i tried to check for date column it throwed error.
For date type instead of CHR(0) what we need to give?

[Updated on: Mon, 19 November 2007 02:41]

Report message to a moderator

Re: TRIGGER [message #281731 is a reply to message #281730] Mon, 19 November 2007 02:44 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
A DATE, obviously.

Or, if you'd like to be highly scientific about it, convert date into a Julian date and work with numbers.
Re: TRIGGER [message #281733 is a reply to message #281661] Mon, 19 November 2007 02:48 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

In case of Varchar2 ; in place of Second parameter , You need to use
Character having no chance to come in that field (CHR(0) as in Example)

In case of Number ; in place of Second parameter , You need to use
Number having no chance to come in that field .

In case of Date ; in place of Second parameter , You need to use
DATE having no chance to come in that field .

Anyway , frank's suggestion will be more suitable for all the datatypes

Thumbs Up
Rajuvan.

[Updated on: Mon, 19 November 2007 02:51]

Report message to a moderator

Previous Topic: Wierd output for even parameter, correct output for an odd one...
Next Topic: Select rows with non zero values
Goto Forum:
  


Current Time: Thu Dec 08 08:32:12 CST 2016

Total time taken to generate the page: 0.14512 seconds