jQuery in Action, 2nd edition*
The moose likes Oracle/OAS and the fly likes creating trigger Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "creating trigger" Watch "creating trigger" New topic
Author

creating trigger

shivani anand
Ranch Hand

Joined: Dec 28, 2000
Posts: 155
Hi! I am trying to create following trigger but getting error..

CREATE OR REPLACE TRIGGER CMPGN_ID_TEST
BEFORE INSERT OR UPDATE
ON SH_TRIGGER_TEST
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
tmpVar NUMBER;
BEGIN
tmpVar := 0;

SELECT ENS_CAMPAIGN_SEQ.NEXTVAL INTO tmpVar FROM dual;
:NEW.CAMPAIGNID := to_char(tmpVar);
-- call store procedure .NewEmailPromo() to insert rows
-- in campaign hierarchy table(Catalog,Segment_Code,Keycode)
-- IF (errCode <> 0)
-- get the errMsg to the user in the GUI(somehow)
call NewEmailPromo(:NEW.CAMPAIGNID,:NEW.CATALOG_CD,:NEW.SEGMENT_CD,:NEW.CAMPAIGN_NAME,:NEW.MESSAGE_NAME,:NEW.LIST_NAME);
EXCEPTION
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
RAISE;

END ;
___/
____________________________________________

error I am getting is
PLS-00103: Encountered the symbol "NEWEMAILPROMO" when expecting one of the following:

:= . ( @ % ;

any idea what's going wrong?

thanks
SA
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1121

SA,
Drop the word "call". Replace this line of your code:

with just this:

Good Luck,
Avi.
shivani anand
Ranch Hand

Joined: Dec 28, 2000
Posts: 155
Thx! Avi it worked. Got some more ques..
I am creating trigger after update of one particcular column in table I know how to do it just on update on table but how to specify only when particular column is updated
____________________________________________________________________
CREATE OR REPLACE TRIGGER test.UPD_MAILOUTPUT
AFTER UPDATE
ON test.MAILOUTPUT

thx in advance
Shivani
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1121

Shivani,
I think "RTFM" is appropriate here -- and if you don't know what it means, look it up at the Free On-Line Dictionary of Computing Web site.

For your information, the syntax for the "CREATE TRIGGER" command is in the "Oracle SQL Reference", which is available from:

http://tahiti.oracle.com

Good Luck,
Avi.
Beksy Kurian
Ranch Hand

Joined: Jul 11, 2001
Posts: 254
Shivani,

here is a sample.

CREATE OR REPLACE TRIGGER "SCHEMA"."MYTRIGGERTEST"
AFTER UPDATE OF "NAME"
ON "TRIGGERTEST"
FOR EACH ROW
BEGIN
do_something;
END;


Beksy
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

Originally posted by Avi Abrami:
if you don't know what it means, look it up at the Free On-Line Dictionary of Computing Web site.



Avi,

Thanks for the link

Shailesh


Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
 
 
subject: creating trigger
 
Similar Threads
Oracle Trigger: delete newly inserted row
trigger to check if a row can be inserted or not
modifing data when Trigger fires
Using Oracle package variables in JDBC.
How to capture the exception in oracle trigger from Java client?