0

I am newbie to oracle. I want to create a trigger to backup table TRIGGER_TEST's rowdata into TABLE TRIGGER_TEST_BK. But showing invalid。

here's the schema and errors:

CREATE TABLE "OA_TEST"."TRIGGER_TEST" 
   (    "ID" VARCHAR2(32) NOT NULL ENABLE, 
    "TYPE" NUMBER, 
    "TEXT" VARCHAR2(255), 
    "UPDATE_TIME" DATE, 
     PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "HCIS4J"  ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "HCIS4J";

CREATE UNIQUE INDEX "OA_TEST"."SYS_C0063741" ON "OA_TEST"."TRIGGER_TEST" ("ID") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "HCIS4J";


CREATE TABLE "OA_TEST"."TRIGGER_TEST_BK" 
   (    "ID" VARCHAR2(32) NOT NULL ENABLE, 
    "TT_ID" VARCHAR2(32) NOT NULL ENABLE, 
    "TYPE" NUMBER, 
    "TEXT" VARCHAR2(255), 
    "UPDATE_TIME" DATE NOT NULL ENABLE, 
     PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "HCIS4J"  ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "HCIS4J";

CREATE UNIQUE INDEX "OA_TEST"."SYS_C0063745" ON "OA_TEST"."TRIGGER_TEST_BK" ("ID") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "HCIS4J";

trigger:

SQL> create or replace trigger OA_TEST.TRI_BK_TRIGGER_TEST AFTER update or DELETE on OA_TEST.TRIGGER_TEST
  2  FOR EACH ROW
  3  begin
  4  insert into OA_TEST.TRIGGER_TEST_BK(ID, TT_ID, TYPE, TEXT, UPDATE_TIME) values(sys_guid(), OLD.ID, OLD.TYPE, OLD.TEXT, sysdate)
  5  end;
  6  /

Warning: Trigger created with compilation errors.

when I show errors;

2/1  PL/SQL: SQL Statement ignored

2/129    PL/SQL: ORA-00933: SQL command not properly ended

3/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
     <an identifier> <a double-quoted delimited-identifier>
     <a bind variable> << close current delete fetch lock insert
     open rollback savepoint set sql execute commit forall merge
     pipe

What am I doing wrong? Can anyone Help?

After adding a semicolon after 'insert into ...' statement.

create or replace trigger OA_TEST.TRI_BK_TRIGGER_TEST AFTER update or DELETE on OA_TEST.TRIGGER_TEST
FOR EACH ROW
begin
    insert into OA_TEST.TRIGGER_TEST_BK(ID, TT_ID, TYPE, TEXT, UPDATE_TIME) values(sys_guid(), OLD.ID, OLD.TYPE, OLD.TEXT, sysdate);
END;

Errors for TRIGGER OA_TEST.TRI_BK_TRIGGER_TEST:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/3  PL/SQL: SQL Statement ignored
2/116    PL/SQL: ORA-00984: column not allowed here

1 Answer 1

3
ORA-00933: SQL command not properly ended

It seems, you are missing a semicolon after your 'insert into ...' statement in the trigger body.

Sign up to request clarification or add additional context in comments.

2 Comments

thanks for answing!but after that errors: Errors for TRIGGER OA_TEST.TRI_BK_TRIGGER_TEST: LINE/COL ERROR -------- ----------------------------------------------------------------- 2/3 PL/SQL: SQL Statement ignored 2/116 PL/SQL: ORA-00984: column not allowed here
@EmmerichLiang - that's because OLD should be :OLD.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.