CODEWITHSUNDEEP
sqldatabaseoracle-databaseplsqltriggers
Matt Farrell
Matt Farrell

Reputation: 199

PL/SQL Trigger issues (copy data from table 1 to table 2)

i have looked around and tried a couple of solutions i have read. i am trying to create a pl/sql trigger that will copy data from one table to another if a clause is met. i feel i may just be making a silly syntax error rather then a full critical failure but would appreciate some help.

create or replace TRIGGER TRG_APPLICATIONS
BEFORE INSERT or UPDATE OF APP_ID, Status_id
ON APPLICATIONS
FOR EACH ROW
BEGIN

:new.APP_ID := SEQ_APP_ID.nextval;
:new.APP_DATE := SYSDATE;

IF STATUS_ID = 2 OR STATUS_ID = 5 OR STATUS_ID = 7 OR STATUS_ID = 8 THEN
INSERT INTO APP_HISTORY
SELECT SRN, STATUS_ID, APP_DATE
FROM APPLICATIONS;
END IF;

END;

here are the errors

6 4 PLS-00201: identifier 'STATUS_ID' must be declared

6 1 PL/SQL: Statement ignored

Upvotes: 0

Views: 464

Answers (3)

Michael Broughton
Michael Broughton

Reputation: 4055

Once you note that you need to reference the values with :NEW to get the current value of STATUS of ID for this insert or update, you will then hit your second error - you can't query the table on which the trigger exists as its content is in flux. You will get a mutating table error. Not to mention that you had no where clause on the SELECT so you would be dumping all of APPLICATIONS into APP_HISTORY. I'm betting that all you want is to copy over the row as it was before the update. Of course there was no row before the insert, so there would be nothing to copy over. Or do you want to copy the NEW values into the HISTORY table on insert?

Assuming that you want to keep the old values on update, then you would:

create or replace TRIGGER TRG_APPLICATIONS
BEFORE INSERT or UPDATE OF APP_ID, Status_id
ON APPLICATIONS
FOR EACH ROW
BEGIN

-- APP_ID better not be the PK or it is changing on UPDATE! 
-- IF you only want this value set once on INSERT, wrap it in an IF INSERTING ... END IF; structure
:new.APP_ID := SEQ_APP_ID.nextval;
:new.APP_DATE := SYSDATE;

IF UPDATING AND ( :NEW.STATUS_ID = 2 OR :NEW.STATUS_ID = 5 OR :NEW.STATUS_ID = 7 OR :NEW.STATUS_ID = 8 )
THEN
   INSERT INTO APP_HISTORY (SRN, STATUS_ID, APP_DATE)
   VALUES (:OLD.SRN, :OLD.STATUS_ID, :OLD.APP_DATE);
END IF;

END;

Other thoughts on your first cut of the trigger - your trigger fires on update of app_id, but then changes the app_id again inside the trigger. So if your UI is setting an APP_ID value and then using that value to insert dependent records - you've just messed that up.

Upvotes: 2

PT_STAR
PT_STAR

Reputation: 505

Check this out:

create or replace TRIGGER TRG_APPLICATIONS
  BEFORE INSERT or UPDATE OF APP_ID, Status_id
  ON APPLICATIONS
  FOR EACH ROW
BEGIN

  :new.APP_ID := SEQ_APP_ID.nextval;
  :new.APP_DATE := SYSDATE;

  IF :new.STATUS_ID = 2 OR 
     :new.STATUS_ID = 5 OR 
     :new.STATUS_ID = 7 OR 
     :new.STATUS_ID = 8 THEN

    INSERT INTO APP_HISTORY (srn, status_id, app_date)
     values (:new.srn, :new.status_id, :new.app_date);

 END IF;

END;

Upvotes: 0

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

probably you need add NEW

IF :NEW.STATUS_ID = 2 OR :NEW.STATUS_ID = 5 
OR :NEW.STATUS_ID = 7 OR :NEW.STATUS_ID = 8 THEN
    INSERT INTO APP_HISTORY (srn, status_id, app_date)
    VALUES (:NEW.SRN, :NEW.STATUS_ID, :NEW.APP_DATE);
END IF;

Upvotes: 0

Related Questions