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 NOCYCLSequence 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 testingSQL> insert into authgroup(authGroupName) values('testing2');1 row created.SQL> select * from authgroup;AUTHGROUPID AUTHGROUPNAME----------- -------------------- 1 testing 2 testing2