开发者社区> 问答> 正文

求助,oracle创建一个简单的触发器报错?报错

一共2张表, TRIGGER_TEST、TRIGGER_TEST_BK。

现在我创建一个触发器, 只要TRIGGER_TEST表有update操作或者delete操作,就插旧记录到TRIGGER_TEST_bk表。

下面是表定义和触发器。

-- 基表

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";
-- BK表

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";

 

-- 触发器

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;
ALTER TRIGGER "OA_TEST"."TRI_BK_TRIGGER_TEST" ENABLE;

 

报错信息:

ORA-24344: 成功, 但出现编译错误
 

我linux登录进数据库后, show errors:

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.

SQL> show errors;
Errors for TRIGGER OA_TEST.TRI_BK_TRIGGER_TEST:

LINE/COL ERROR
-------- -----------------------------------------------------------------
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

 

 

 

 

 

展开
收起
爱吃鱼的程序员 2020-06-06 20:54:31 515 0
1 条回答
写回答
取消 提交回答
  • https://developer.aliyun.com/profile/5yerqm5bn5yqg?spm=a2c6h.12873639.0.0.6eae304abcjaIB
                        :old.字段名 
                    
    
                            是你了, 谢谢老哥
                        
    
                        <p>楼上正解</p>
    
                    
    
                        <div class='ref'><h4>引用来自“dl123456”的评论</h4>:old.字段名 
    
    2020-06-06 20:54:50
    赞同 展开评论 打赏
问答排行榜
最热
最新

相关电子书

更多
PostgresChina2018_樊文凯_ORACLE数据库和应用异构迁移最佳实践 立即下载
PostgresChina2018_王帅_从Oracle到PostgreSQL的数据迁移 立即下载
Oracle云上最佳实践 立即下载