+ -
当前位置:首页 → 问答吧 → 创建关键字自动增加的触发器出现问题了,求帮助

创建关键字自动增加的触发器出现问题了,求帮助

时间:2011-12-22

来源:互联网

SQL code

create sequence customerid increment by 1 start with 1 maxvalue 999999999

create or replace trigger bef_ins_customer_autoid
before insert on customer 
referencing new as newrow
for each row
when(newrow.id is null)
begin
  select customerid.nextval into :newrow.id  from dual
end;



但是编译时有warning,而且真正插入语句时也无法插入,
SQL Error: ORA-04098: 触发器 'bef_ins_customer_autoid' 无效且未通过重新验证
04098. 00000 - "trigger '%s.%s' is invalid and failed re-validation"
*Cause: A trigger was attempted to be retrieved for execution and was
  found to be invalid. This also means that compilation/authorization
  failed for the trigger.
*Action: Options are to resolve the compilation/authorization errors,
  disable the trigger, or drop the trigger

作者: qinghuer   发布时间: 2011-12-22

实测创建触发器
SQL code

CREATE OR REPLACE TRIGGER bef_ins_customer_autoid
BEFORE INSERT ON Customer 
REFERENCING NEW AS newrow
FOR EACH ROW
WHEN (newrow.id IS NULL)
BEGIN
  SELECT customerid.nextval into :newrow.id  FROM dual;
END;



实测结果:

作者: LuiseRADL   发布时间: 2011-12-22

学习了

作者: gaozhenchao_jia   发布时间: 2011-12-22

引用 1 楼 luiseradl 的回复:

实测创建触发器
SQL code

CREATE OR REPLACE TRIGGER bef_ins_customer_autoid
BEFORE INSERT ON Customer
REFERENCING NEW AS newrow
FOR EACH ROW
WHEN (newrow.id IS NULL)
BEGIN
SELECT customerid.nextval into……

嗯?这是什么情况?为什么我这边执行SQL code

    insert into Customer(id,name,phonenum,address)
    values (null,'xiaowang','1412341234','beijing') 


总是出现错误呢?我用的oracle sqldeveloper 会不会和这个有关系?但是不应该啊?

作者: qinghuer   发布时间: 2011-12-22