CODEWITHSUNDEEP
oracle-databaseplsqltriggers
yunyun333
yunyun333

Reputation: 131

Error when trying to create a new trigger - not ended properly

I am trying to create a trigger so that whenever a row is updated or deleted in table 1, a copy of its original is created in table 2. The sql looks as follows

create or replace trigger TRIG_table_2 BEFORE update or delete ON table_1 REFERENCING NEW AS NEW OLD AS OLD  for EACH ROW
BEGIN 
INSERT INTO table_2( 
    id
    ,value
    ,date_collected
    ,patient_id
) VALUES (
    :OLD.id
    , :OLD.value
    , :OLD.date_collected
    , :OLD.patient_id
);
END;

however when I run this, I get an error saying that it ran successfully but with errors. I checked user_errors table, and found this:

    PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

   ;

I figured it must be an error with how the sql is terminated, but I can't figure out how to fix it.

Upvotes: 0

Views: 48

Answers (1)

Littlefoot
Littlefoot

Reputation: 143103

As commented, we need trigger code. Complete code.

@astentx said that forward slash is missing; most probably not. I guess that semi-colon is missing (as the OP's error message suggests). Something like this:

SQL> create or replace trigger trg_test
  2    before update on test
  3    for each row
  4  begin
  5    null                    --> semi-colon missing here
  6  end;
  7  /                         --> slash would create a trigger; without it,
                               --> cursor would just keep blinking 
Warning: Trigger created with compilation errors.

So what's wrong with it?

SQL> show err
Errors for TRIGGER TRG_TEST:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/1      PLS-00103: Encountered the symbol "END" when expecting one of the
         following:
         ;
         The symbol ";" was substituted for "END" to continue.

SQL>

Right; the same error OP reported. But, to know it exactly, we still miss exact trigger code.

[EDIT] Code which was posted a minute ago is OK:

SQL> create or replace trigger TRIG_table_2
  2  BEFORE update or delete ON table_1 REFERENCING NEW AS NEW OLD AS OLD
  3  for EACH ROW
  4  BEGIN
  5  INSERT INTO table_2(
  6      id
  7      ,value
  8      ,date_collected
  9      ,patient_id
 10  ) VALUES (
 11      :OLD.id
 12      , :OLD.value
 13      , :OLD.date_collected
 14      , :OLD.patient_id
 15  );
 16  END;
 17  /

Trigger created.

SQL>

Upvotes: 1

Related Questions