Home » SQL & PL/SQL » SQL & PL/SQL » Triggers (10.2.0.1, Windows 2003)
Triggers [message #350813] Fri, 26 September 2008 08:53 Go to next message
sant_new
Messages: 165
Registered: June 2008
Senior Member
Hi friends,

I've created an after insert trigger for table 'test' to insert a record into another table 'pool'. Below is the trigger:

CREATE OR REPLACE TRIGGER test_trg
after insert ON test for each row
begin
if :new.orgn != 1 then
insert into pool (key,cust,name,phone)
values (:new.ordkey,'elem',:new.name,:new.phone);
end if;
end slcstinsert_trg;
/


The trigger should insert the record into POOL when test.orgn is not equal to 1 and if the test.orgn is = 1, the record should be rejected, in other words the trigger should not be fired to insert that record into POOL.

I've used if and end if statement in the trigger. If I want to use if then else, I am not sure what the else statement could be.

Please help me if you have suggestions.

CREATE OR REPLACE TRIGGER test_trg
after insert ON test for each row
begin
if :new.orgn != 1 then
insert into pool (key,cust,name,phone)
values (:new.ordkey,'elem',:new.name,:new.phone);
[B]else ????????????[/B]
end if;
end slcstinsert_trg;
/
Re: Triggers [message #350826 is a reply to message #350813] Fri, 26 September 2008 10:02 Go to previous messageGo to next message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
Remember to read manuals first

Bye Alessandro
Re: Triggers [message #350832 is a reply to message #350813] Fri, 26 September 2008 11:00 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
sant_new wrote on Fri, 26 September 2008 15:53

I've used if and end if statement in the trigger. If I want to use if then else, I am not sure what the else statement could be.


Maybe I misinterpret your requirements, but you stated that you do NOT want to do the extra insert into POOL if the field equals 1 (i.e. in case of the "else" condition)
In other words, "else" you want to do nothing, so there is nothing to do in your else-branch, hence you should not add an else.
Re: Triggers [message #350834 is a reply to message #350813] Fri, 26 September 2008 11:05 Go to previous messageGo to next message
Ronald Beck
Messages: 121
Registered: February 2003
Senior Member
Are you getting any type of error message? Is the trigger not doing what you expected? What exactly are you asking? If you want an IF ... Then... else... END IF, what do you want in the ELSE portion?

You have very little useful information in your post. If you're having a problem, show the error message. If you can't figure out what you want your trigger to do if :new.orgn = 1, you need to review your requirements. If you don't want it to do anything if :new.orgn = 1, then don't put in an ELSE clause.

Ron
Re: Triggers [message #350842 is a reply to message #350813] Fri, 26 September 2008 12:53 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Beware of the NULL.
Re: Triggers [message #350855 is a reply to message #350813] Fri, 26 September 2008 13:51 Go to previous messageGo to next message
sant_new
Messages: 165
Registered: June 2008
Senior Member
The default value of orgn is set to 0. So I hope I dont have to mention NVL in the trigger.

CREATE OR REPLACE TRIGGER test_trg
after insert ON test for each row
begin
if :new.orgn != 1 then
insert into pool (key,cust,name,phone)
values (:new.ordkey,'elem',:new.name,:new.phone);
else null;
end if;
end testinsert_trg;
/


Thanks everyone.
Re: Triggers [message #350857 is a reply to message #350813] Fri, 26 September 2008 13:53 Go to previous messageGo to next message
sant_new
Messages: 165
Registered: June 2008
Senior Member
I need your help with a similar update trigger.

CREATE OR REPLACE TRIGGER test2_trg
AFTER UPDATE ON test2 FOR EACH ROW
declare
scust varchar2(10);

BEGIN 

select sldcust into scust from sold where sold.addcode = :new.addcode;

update pool
set key = :new.inbt
where acctno = scust;

EXCEPTION 
WHEN OTHERS THEN 
<EXCEPTION Handling>;
END test2trg;
/


In this trigger, I need to have a condition that only when the sold.orgn != 1, the trigger should update the pool table. I think we cannot use sold.orgn in the if clause.

I'm not sure how I can have a condition here. ANy help is appreciated.Thanks.


Re: Triggers [message #350863 is a reply to message #350857] Fri, 26 September 2008 14:30 Go to previous message
Michel Cadot
Messages: 64116
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You do it wrong.
This must be done in a procedure and NOT in a trigger.

And forget "WHEN OTHERS", it is a bug.

Regards
Michel
Previous Topic: Use of => in PLSQL variable definitions
Next Topic: Inserting duplicate records
Goto Forum:
  


Current Time: Tue Dec 06 02:09:11 CST 2016

Total time taken to generate the page: 0.06893 seconds