Home » SQL & PL/SQL » SQL & PL/SQL » Select Statement inside a Trigger (Oracle)
Select Statement inside a Trigger [message #324448] Mon, 02 June 2008 09:02 Go to next message
sbadriprasad
Messages: 14
Registered: September 2007
Junior Member
hi,

i want to write a trigger in which i want to check some condition and then process. for checking the condition i want to take a value from another table. how can i have a select statement inside the trigger.

thank you.
Re: Select Statement inside a Trigger [message #324449 is a reply to message #324448] Mon, 02 June 2008 09:07 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>how can i have a select statement inside the trigger.
By writing valid PL/SQL in trigger.
Re: Select Statement inside a Trigger [message #324450 is a reply to message #324448] Mon, 02 June 2008 09:07 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
It's like how you code a normal pl/sql block. Try it and if you are stuck somewhere come back here, post the code and the error and I am sure somebody will help you out to fix the problem.

Regards

Raj
Re: Select Statement inside a Trigger [message #324452 is a reply to message #324450] Mon, 02 June 2008 09:25 Go to previous messageGo to next message
sbadriprasad
Messages: 14
Registered: September 2007
Junior Member
my trigger is

CREATE OR REPLACE TRIGGER TRG_WLT_UPDATE BEFORE UPDATE OF ACT_FUN ON DBOR.WLT FOR EACH ROW
BEGIN

DECLARE Bkp VARCHAR2(10);
bkp=SELECT BSHOP_PROCESS FROM wlInfo l Left Outer Join LNPRO p ON l.LProgram=p.LPGM WHERE l.Loan=:OLD.Loan;
IF (NVL(UPPER(bkp),CHR(0))='JAX')
--trigger process comes here
END IF;
END;
/

i am getting the following error
4/4 PLS-00103: Encountered the symbol "=" when expecting one of the following:
constant exception <an identifier>
<a double-quoted delimited-identifier> table LONG_ double ref
char time timestamp interval date binary national character
nchar



can any one help me out.
Re: Select Statement inside a Trigger [message #324455 is a reply to message #324452] Mon, 02 June 2008 09:40 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8621
Registered: November 2002
Location: California, USA
Senior Member
In pl/sql you have to select INTO your variable:

SELECT BSHOP_PROCESS
INTO bkp
FROM wlInfo l Left Outer Join LNPRO p ON l.LProgram=p.LPGM
WHERE l.Loan=:OLD.Loan;

P.S. And your DECLARE and BEGIN are in the wrong order.

[Updated on: Mon, 02 June 2008 10:10]

Report message to a moderator

Re: Select Statement inside a Trigger [message #324462 is a reply to message #324448] Mon, 02 June 2008 10:06 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
The more basic problem is OP does not know how to construct a valid variable assignment statement in PL/SQL.

[Updated on: Mon, 02 June 2008 10:06] by Moderator

Report message to a moderator

Re: Select Statement inside a Trigger [message #324554 is a reply to message #324455] Tue, 03 June 2008 02:15 Go to previous messageGo to next message
sbadriprasad
Messages: 14
Registered: September 2007
Junior Member
hi,
it is working fine.
SELECT BSHOP_PROCESS
INTO bkp
FROM wlInfo l Left Outer Join LNPRO p ON l.LProgram=p.LPGM
WHERE l.Loan=:OLD.Loan;

but if the join doesnt return any record then we are getting the error.

ORA-01403: no data found
ORA-06512: at line 3

how to hadle this.
Note: i am very new to oracle.

Re: Select Statement inside a Trigger [message #324557 is a reply to message #324554] Tue, 03 June 2008 02:22 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You should start reading up some books on PL/SQL programming, follow some tutorials, and start learning how to do some research.
Coming here with each and every error you encounter will lead to people ignoring you, I'm afraid.
We like to see people putting in some effort themselves.
Re: Select Statement inside a Trigger [message #324650 is a reply to message #324554] Tue, 03 June 2008 08:50 Go to previous message
Barbara Boehmer
Messages: 8621
Registered: November 2002
Location: California, USA
Senior Member
http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/errors.htm#LNPLS007
Previous Topic: Problem: Whats wrong with this PL/SQL procedure?
Next Topic: DBMS_SCHEDULER
Goto Forum:
  


Current Time: Fri Dec 02 23:18:56 CST 2016

Total time taken to generate the page: 0.04685 seconds