--建表
create table users(
id NUMBER(5) PRIMARY KEY,
username varchar2(20) not null,
password varchar2(20),
email varchar2(20)
);
--建序列
CREATE SEQUENCE users_seq
MINVALUE 1
MAXVALUE 9999999999
START WITH 1
INCREMENT BY 1
CACHE 30
ORDER;
--建触発器
CREATE OR REPLACE TRIGGER users_tg
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
SELECT users_seq.NEXTVAL INTO :NEW.id FROM DUAL;
END;
--執行
insert into users (id,username,password,email) values(users_seq.NEXTVAL,'www','123','[email protected]');select * from users;
到执行插入语句时提示:ORA-04098触发器"TEST1.USERS_TG"无效且未通过重新验证
create table users(
id NUMBER(5) PRIMARY KEY,
username varchar2(20) not null,
password varchar2(20),
email varchar2(20)
);
--建序列
CREATE SEQUENCE users_seq
MINVALUE 1
MAXVALUE 9999999999
START WITH 1
INCREMENT BY 1
CACHE 30
ORDER;
--建触発器
CREATE OR REPLACE TRIGGER users_tg
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
SELECT users_seq.NEXTVAL INTO :NEW.id FROM DUAL;
END;
--執行
insert into users (id,username,password,email) values(users_seq.NEXTVAL,'www','123','[email protected]');select * from users;
到执行插入语句时提示:ORA-04098触发器"TEST1.USERS_TG"无效且未通过重新验证
执行一下:
drop trigger users_tg;
create or replace trigger t_id_tg before insert on users
for each row
begin
select users_seq.nextval into :new.id from dual;
end t_id_tg;
/
再试试,肯定没有问题。
DISABLED--表示未启用
SQL> CREATE OR REPLACE TRIGGER users_tg
2 BEFORE INSERT ON users
3 FOR EACH ROW
4 BEGIN
5 SELECT users_seq.NEXTVAL INTO :NEW.id FROM DUAL;
6 END;
7 /警告: 创建的触发器带有编译错误。SQL> show error
TRIGGER USERS_TG 出现错误:LINE/COL ERROR
-------- -----------------------------------------------------------------
2/2 PLS-00103: 出现符号 ""在需要下列之一时:
begin case declare 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
<a single-quoted SQL string> pipe
去掉之后, 没问题了:SQL> CREATE OR REPLACE TRIGGER users_tg
2 BEFORE INSERT ON users
3 FOR EACH ROW
4 BEGIN
5 SELECT users_seq.NEXTVAL INTO :NEW.id FROM DUAL;
6 END;
7 /触发器已创建SQL> drop trigger users_tg;触发器已丢弃SQL> insert into users (id,username,password,email) values(users_seq.NEXTVAL,'ww
w','123','[email protected]');已创建 1 行。SQL> select * from users; ID USERNAME PASSWORD EMAIL
---------- -------------------- -------------------- --------------------
2 www 123 [email protected]
4 www 123 [email protected]
5 www 123 [email protected]>