一共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
:old.字段名
是你了, 谢谢老哥
<p>楼上正解</p>
<div class='ref'><h4>引用来自“dl123456”的评论</h4>:old.字段名
版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。