建立了两个表 change 和 test对test写触发器 现在只能将插入修改的 系统时间 插入到change表中
CREATE OR REPLACE TRIGGER "ITSM"."CHANGTEST" AFTER
INSERT
OR UPDATE OF "TID", "TNAME" ON "T_TEST"REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
DECLARE
tmpVar:=0;BEGIN if :new.tname <> :old.tname and :new.tname not is null then insert into T_CHANGE (CHANGECON,CHANGETIME) values( :new.tname, sysdate);
End if;
END ;
上面是写的加入插入内容的部分 但总是报错。 大家看看怎么写可以将修改的内容 插入到change表中。
CREATE OR REPLACE TRIGGER "ITSM"."CHANGTEST" AFTER
INSERT
OR UPDATE OF "TID", "TNAME" ON "T_TEST"REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
DECLARE
tmpVar:=0;BEGIN if :new.tname <> :old.tname and :new.tname not is null then insert into T_CHANGE (CHANGECON,CHANGETIME) values( :new.tname, sysdate);
End if;
END ;
上面是写的加入插入内容的部分 但总是报错。 大家看看怎么写可以将修改的内容 插入到change表中。
not is null
测试:
declare
v_tmp number(4);
begin
if v_tmp is not null then
v_tmp :=1;
end If;
end;
应该改为:new.tanme is not null,前面两位同学已经答复了.另外
if :new.tname <> :old.tname and :new.tname is not null then
这个语句是有风险的,如果 :old.tanme 为null的话,那结果就完全不对了,
比如:new.tanme为null,而:old.tanme也为null,那么这个语句就麻烦了,所以正确的应该是
if :new.tanme <> :old.tanme and :new.tanme is not null and :old.tanme is not null then
...
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
DECLARE
BEGINif :new.tanme <> :old.tanme and :new.tanme is not null and :old.tanme is not null thenthen insert into T_CHANGE (CHANGECON,CHANGETIME) values( :new.tname, sysdate);
End if;
END ; 但因为有前两行 报这个 ORA-04079:无效的触发器说明
行号= 4 列号= 2 错误文本= PL/SQL: Statement ignored
values( :new.tname, sysdate);字段名不同,有一个肯定是笔误
AFTER INSERT
OR UPDATE OF "TID", "TNAME" ON "T_TEST"REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
DECLARE
tmpVar number:=0; --未声明ympVar的类型
BEGIN
if :new.tname <> :old.tname
and :new.tname is not null then --逻辑表用分号引用,不要有空格
insert into T_CHANGE (CHANGECON,CHANGETIME) values( :new.tname, sysdate);
End if;
END ;
CREATE OR REPLACE TRIGGER "TESTCHANGE" AFTER
INSERT
OR UPDATE OF "TNAME" ON "T_TEST" FOR EACH ROW REFERENCING NEW AS New OLD AS Old
DECLARE
tmpVar number:=0;
BEGINif :new.tname <> :old.tname and :new.tname is not null and :old.tname is not null then insert into T_CHANGE (CHANGECON,CHANGETIME) values( :new.tname, sysdate);
End if;
END ; 好像一样啊 在视图页面一点应用就报无效触发器
OR UPDATE OF "TNAME" ON "T_TEST" FOR EACH ROW REFERENCING NEW AS New OLD AS Old
应该是:
OR UPDATE OF "TNAME" ON "T_TEST" REFERENCING NEW AS New OLD AS Old FOR EACH ROW
而且REFERENCING字句只是为:old和:new指定别名,如果LZ在触发器中没有用到别名就不要用这个REFERENCING了。--------------------------------
正在找数据库开发方面的工作,对SQL Server 2k 比较熟悉,对Oracle结构体系和PL/SQL也有些了解.