CODEWITHSUNDEEP
sqloracle-databasetriggersoracle11g
Vivek
Vivek

Reputation: 11

Error on executing trigger

CREATE OR REPLACE TRIGGER PROCESS_POPULATE_INSTANCE
AFTER INSERT OR UPDATE ON PROCESS_INSTANCE
FOR EACH ROW
DECLARE
 InstanceExists NUMBER;
BEGIN
 SELECT COUNT(*)
 INTO InstanceExists
 FROM TEST_PROCESSDATA
 WHERE TEST_PROCESSDATA.PROCESS_INSTANCE_ID = :NEW.INSTANCE_ID ;

 IF ( InstanceExists > 0 ) THEN
UPDATE TEST_PROCESSDATA SET PROCESS_STATUS =:NEW.STATUS WHERE PROCESS_INSTANCE_ID = NEW.INSTANCE_ID;
 ELSIF 
 INSERT INTO TEST_PROCESSDATA (PROCESS_INSTANCE_ID,PROCESS_STATUS, STARTED_TIME) VALUES (:NEW.INSTANCE_ID,:NEW.STATUS,:NEW.START_TIME);
 END IF;
END PROCESS_POPULATE_APPDATA;

On executing the above trigger, i get the below error:

Error(12,2): PLS-00103: Encountered the symbol "INSERT" when expecting one of the following: ( - + case mod new not null continue avg count current exists max min prior sql stddev sum variance execute forall merge time timestamp interval
date pipe
Error(13,2): PLS-00103: Encountered the symbol "END"

Upvotes: 1

Views: 1140

Answers (2)

Nick Krasnov
Nick Krasnov

Reputation: 27251

In addition to what Ravindra bagale has already noted I would add the following:

First. In the statement 

UPDATE TEST_PROCESSDATA SET PROCESS_STATUS =:NEW.STATUS 
 WHERE PROCESS_INSTANCE_ID = NEW.INSTANCE_ID;

: colon is missing in front of NEW.INSTANCE_ID

And second. You might consider using of merge statement instead of IF .. THEN .. ELSE.. END IF construct and additional select statement. For example.

create or replace trigger process_populate_instance     
after insert or update on process_instance 
for each row
begin

   merge into test_processdata
   using dual
     on (process_instance_id = :new.instance_id)
   when matched
    then update
            set process_status =:new.status
   when not matched
   then insert (process_instance_id,process_status, started_time) 
        values (:new.instance_id,:new.status,:new.start_time);

end;

Upvotes: 2

Ravindra Bagale
Ravindra Bagale

Reputation: 17655

use ELSE instead of ELSIF
here u used elseif,but not used else, u can't use elseif without else
when there are more than two way then u can use elsif.

IF ( InstanceExists > 0 ) THEN
UPDATE TEST_PROCESSDATA SET PROCESS_STATUS =:NEW.STATUS WHERE PROCESS_INSTANCE_ID = NEW.INSTANCE_ID;
 ELSE 
 INSERT INTO TEST_PROCESSDATA (PROCESS_INSTANCE_ID,PROCESS_STATUS, STARTED_TIME) VALUES (:NEW.INSTANCE_ID,:NEW.STATUS,:NEW.START_TIME);
 END IF;
END PROC

Upvotes: 1

Related Questions