日期:2014-05-17  浏览次数:21012 次

简单自增触发器,执行有问题,帮忙看看
CREATE TABLE authGroup
(
  authGroupID INT NOT NULL,  
  authGroupName VARCHAR2(20)
);
 ALTER TABLE authGroup ADD PRIMARY KEY (authGroupID); 

 CREATE SEQUENCE S_authGroupID INCREMENT BY 1 START WITH 1 NOMAXVALUE NOCYCLE;
 CREATE OR REPLACE TRIGGER authGroupTrigger
  BEFORE INSERT ON authGroup FOR EACH ROW  
BEGIN  
SELECT S_authGroupID.NEXTVAL INTO :authGroup.authGroupID FROM DUAL;
END;  

建的一个自增触发器,可当:
INSERT INTO authGroup(authGroupName) VALUES('default');
时却出错:ORA-04098: trigger 'AUTHGROUPTRIGGER' is invalid and failed re-validation
大家帮忙看一下,触发器是不是错了


------解决方案--------------------
CREATE OR REPLACE TRIGGER authGroupTrigger 
BEFORE INSERT ON authGroup FOR EACH ROW
BEGIN
SELECT S_authGroupID.NEXTVAL INTO :new.authGroupID FROM DUAL; 
END;

------解决方案--------------------
SQL code

你的代码中存在中文";",修改后就可以了,参考如下:
CREATE TABLE authGroup 
( 
  authGroupID  INT  NOT NULL,  
  authGroupName VARCHAR2(20) 
); 
ALTER TABLE authGroup ADD PRIMARY KEY (authGroupID); 

CREATE SEQUENCE S_authGroupID INCREMENT BY 1 START WITH 1 NOMAXVALUE NOCYCLE; 

CREATE OR REPLACE TRIGGER authGroupTrigger 
  BEFORE INSERT ON authGroup FOR EACH ROW  
BEGIN  
SELECT S_authGroupID.NEXTVAL INTO :new.authGroupID FROM DUAL;
END;  
/
INSERT INTO authGroup(authGroupName) VALUES('default'); 
SELECT * FROM authgroup;

输出:
1    default

------解决方案--------------------
我这边ok,

SQL code

QL> CREATE TABLE authGroup
  2  (
  3    authGroupID  INT  NOT NULL,
  4    authGroupName VARCHAR2(20)
  5  );

Table created.

SQL> ALTER TABLE authGroup ADD PRIMARY KEY (authGroupID);

Table altered.

SQL> CREATE SEQUENCE S_authGroupID INCREMENT BY 1 START WITH 1 NOMAXVALUE NOCYCL

Sequence created.

SQL> CREATE OR REPLACE TRIGGER authGroupTrigger
  2    BEFORE INSERT ON authGroup FOR EACH ROW
  3  BEGIN
  4   SELECT S_authGroupID.NEXTVAL INTO :new.authGroupID FROM DUAL;
  5  END;
  6  /

Trigger created.


SQL> insert into authgroup(authGroupName) values('testing');

1 row created.

SQL> select * from authgroup;

AUTHGROUPID AUTHGROUPNAME
----------- --------------------
          1 testing

SQL> insert into authgroup(authGroupName) values('testing2');

1 row created.

SQL> select * from authgroup;

AUTHGROUPID AUTHGROUPNAME
----------- --------------------
          1 testing
          2 testing2