Scott Leslie Scott Leslie - 1 month ago 5
SQL Question

SQL - Trigger is invalid and failed re-validation

When a user creates an application for the table 'application' I want a copy placed into the table 'application_history'. I've tried creating a trigger and get the error that is in the title.

create or replace TRIGGER create_history AFTER INSERT ON application
FOR EACH ROW
DECLARE
BEGIN
insert into application_history values (:new.arn, :new.srn, :new.job_id, :new.application_status);
END;


Any help will be much appreciated.

Answer

As Justin suspected, you seem to be having more than 4 columns in application_history. Either provide values for all columns or specify columns lie 'insert into table1 (col1, col2) values (1,2)'

It worked for me after adding extra column 'hist_id'(just to give you an idea) ...

create table application (arn number, srn number, job_id number, application_status char(1) );

create table application_history (hist_id number, arn number, srn number, job_id number, application_status char(1) );

create or replace TRIGGER create_application_history AFTER INSERT ON application
FOR EACH ROW
DECLARE
BEGIN
  insert into application_history values (1, :new.arn, :new.srn, :new.job_id, :new.application_status);
END;
/