一个简单的触发器,由于oracle中时间戳不能像sqlserver那样自动更新,所以自己写了个触发器来实现添加、修改时的自动更改.CREATE OR REPLACE TRIGGER AUTHGROUP_TRIGGER
BEFORE INSERT OR UPDATE ON authGroup
FOR EACH ROW
BEGIN
:NEW.stampTime := SYSTIMESTAMP;
END;奇怪的是这个触发器在C#代码中用command执行后,再执行下面的语句INSERT INTO authGroup(authGroupID,authGroupName,owner)VALUES(AUTHGROUP_SEQ.NEXTVAL,'Super','admin')
ORA-04098: trigger 'SYSTEM.AUTHGROUP_TRIGGER' is invalid and failed re-validation提示为无效。看看语法也没什么错误,就拿到SQL plus下和vs2005下的服务器资源管理器下执行,结果都可以使用。很奇怪了。都是用system登录的.SQL>
SQL> CREATE OR REPLACE TRIGGER AUTHGROUP_TRIGGER
2 BEFORE INSERT OR UPDATE ON authGroup
3 FOR EACH ROW
4 BEGIN
5 :NEW.stampTime := SYSTIMESTAMP;
6 END;
7 /触发器已创建SQL> INSERT INTO authGroup(authGroupID,authGroupName,owner)VALUES(AUTHGROUP_SEQ.NEXTVAL,'Super','admin');已创建1行真是不知道原因在哪了?
SQL> select owner, object_name, object_type, status from dba_objects where object_name='AUTHGROUP_TR
IGGER';OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE STATUS
------------------- -------
SYSTEM
AUTHGROUP_TRIGGER
TRIGGER INVALID
BEFORE INSERT OR UPDATE ON authGroup
FOR EACH ROW
BEGIN
:NEW.stampTime := SYSTIMESTAMP;
END;奇怪的是这个触发器在C#代码中用command执行后,再执行下面的语句INSERT INTO authGroup(authGroupID,authGroupName,owner)VALUES(AUTHGROUP_SEQ.NEXTVAL,'Super','admin')
ORA-04098: trigger 'SYSTEM.AUTHGROUP_TRIGGER' is invalid and failed re-validation提示为无效。看看语法也没什么错误,就拿到SQL plus下和vs2005下的服务器资源管理器下执行,结果都可以使用。很奇怪了。都是用system登录的.SQL>
SQL> CREATE OR REPLACE TRIGGER AUTHGROUP_TRIGGER
2 BEFORE INSERT OR UPDATE ON authGroup
3 FOR EACH ROW
4 BEGIN
5 :NEW.stampTime := SYSTIMESTAMP;
6 END;
7 /触发器已创建SQL> INSERT INTO authGroup(authGroupID,authGroupName,owner)VALUES(AUTHGROUP_SEQ.NEXTVAL,'Super','admin');已创建1行真是不知道原因在哪了?
SQL> select owner, object_name, object_type, status from dba_objects where object_name='AUTHGROUP_TR
IGGER';OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE STATUS
------------------- -------
SYSTEM
AUTHGROUP_TRIGGER
TRIGGER INVALID
已经用你的trigger在我机器上试过了,没哟问题,确认一下你表结构有没有修改过。
觉得像是权限的问题
可是都是用的相同用户登录system
做了对比实验:
INSERT INTO authGroup(authGroupID,authGroupName,owner)VALUES(AUTHGROUP_SEQ.NEXTVAL,'Super','admin');
1.在代码中执行生成触发器,再执行上一句添加
结果是:ORA-04098: trigger 'SYSTEM.AUTHGROUP_TRIGGER' is invalid and failed re-validation
2.把在1.中生成触发器直接复制出来,放在SQL plus下和vs2005下的服务器资源管理器下执行,再执行上一句添加
结果是:已创建1行觉得太奇怪了
只要放在C#代码中生成触发器,添加时就是触发器无效。
可把触发器语句复制出来,放在SQL plus下生成替换这个触发器,再在代码中执行添加,却是成功回头再在代码中执行生成替换触发器,再添加就失败了(还是触发器无效)怀疑是触发器语句开始或结束的空格之类导致的问题,仔细检查也不是这个原因。
人家说你的TRIGGER无效,无效的当然是运行不了了
(
authGroupID INT NOT NULL,
authGroupName VARCHAR2(20) NOT NULL,
stampTime TIMESTAMP NOT NULL,
owner VARCHAR2(20)
);
ALTER TABLE authGroup ADD PRIMARY KEY (authGroupID);
DROP SEQUENCE AUTHGROUP_SEQ;
CREATE SEQUENCE AUTHGROUP_SEQ INCREMENT BY 1 START WITH 1 NOMAXVALUE NOCYCLE;
CREATE OR REPLACE TRIGGER AUTHGROUP_TRIGGER
BEFORE INSERT OR UPDATE ON authGroup
FOR EACH ROW
BEGIN
:NEW.stampTime := SYSTIMESTAMP;
END;
INSERT INTO authGroup(authGroupID,authGroupName,owner)VALUES(AUTHGROUP_SEQ.NEXTVAL,'Super','admin');
在脚本的
CREATE OR REPLACE TRIGGER AUTHGROUP_TRIGGER
BEFORE INSERT OR UPDATE ON authGroup
FOR EACH ROW
BEGIN
:NEW.stampTime := SYSTIMESTAMP;
END; 下加个/ 以后在试试
在8I的CLIENT执行有问题,10G的CLIENT下就没问题了
你换个新版的CLIENT试试吧
如果是程序中,换个JDBC的驱动试试吧