Issue with trigger for archiving

0
votes

Trying to make a trigger that puts data into an archive table when a column called COMPLETION_STATUS goes from incomplete to complete, the dbms is a placeholder for the insert but I'm getting the following errors in the if statement

Error(6,1): PLS-00103: Encountered the symbol enter code here"SELECT" when expecting one of the following: begin function pragma procedure subtype type current cursor delete exists prior The symbol "begin" was substituted for "SELECT" to continue.

Error(9,1): PLS-00103: Encountered the symbol "IF" when expecting one of the following: * & - + ; / at for mod remainder rem and or group having intersect minus order start union where connect || multiset The symbol ";" was substituted for "IF" to continue.

Error(13,4): PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: ( begin case declare end exception exit for goto if loop mod null pragma raise return select update while with << continue close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe purge

Code:

create or replace TRIGGER ARCHIVING_TRIG 
BEFORE UPDATE OF COMPLETION_STATUS ON PROJECT_DATA

BEGIN
DECLARE COMPLETION_STATUS1 VARCHAR2(9);
SELECT COMPLETION_STATUS into COMPLETION_STATUS1
FROM PROJECT_DATA WHERE COMPLETION_STATUS = 'complete'

IF COMPLETION_STATUS1 = 'complete'
THEN 
DBMS.output('123');
END IF;
END;
2
sqloracletriggers
which data you want to put into another table? on update?? – nikhil sugandh

2 Answers

1
votes
  • The DECLARE block should be before the BEGIN block.
  • The SELECT ... statement needs to be terminated with a semicolon (;).
  • It's dbms_output.put_line() not dbms.output();
  • You're trying to assign the result of a query that potentially can return more than one row to a scalar variable.
  • The rows selected from project_data have no relation to the one(s) that triggered the trigger.

I suggest you use something like:

CREATE TRIGGER archiving_trig
               AFTER UPDATE
                     ON project_data
               FOR EACH ROW
               WHEN (old.completion_status <> 'complete'
                     AND new.completion_status = 'complete')
BEGIN
  dbms_output.put_line('Trigger fired for ID ' || :new.id);
END;

db<>fiddle

  • I think maybe AFTER is the better time, because you want to archive the row after the status was successfully changed.
  • Because of the WHEN the trigger will only fire if completion_status has been changed from something other than 'complete' to 'complete'. But you maybe also need to have a method of removing entries from the archive when the status changes from 'complete' to something else. That isn't covered here.
  • Declaring it as FOR EACH ROW let's you access the values of the updated row via :new. That way you don't need a query to select that nor a variable to select into.
0
votes

I guess you need this:

create table PROJECT_DATA_NEW as select * from PROJECT_DATA where 1=2;


CREATE OR REPLACE TRIGGER ARCHIVING_TRIG 
AFTER UPDATE
   ON PROJECT_DATA
   FOR EACH ROW

DECLARE
status number;
BEGIN
status:=0;
select 1 into status from PROJECT_DATA where 
:new.COMPLETION_STATUS='complete' and 
:old.COMPLETION_STATUS='incomplete'

if (status=1) then
insert into PROJECT_DATA_NEW values(:old.column1,
:old.column2,
:old.column3,
:old.column4,
:old.column5,....etc); 
end if;
END;

/