• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

creating trigger

 
shivani anand
Ranch Hand
Posts: 155
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1141
1
Java Oracle
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
SA,
Drop the word "call". Replace this line of your code:

with just this:

Good Luck,
Avi.
 
shivani anand
Ranch Hand
Posts: 155
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1141
1
Java Oracle
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 254
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1082
Java Oracle Spring
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
I agree. Here's the link: http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic