baozhadehulu
baozhadehulu
采纳率52.6%
2016-05-15 02:57 阅读 2.1k
已采纳

Oracle触发器不能读取表的问题

1

12、假设有这样一张用户表表结构如下:UserInfo(id ,username,userPass),希望向表中增加数据时,表中id列的数字自动生成。(选做)
1)第一步创建序列,要求开始的数字为1,每次递增1,按顺序产生序列值;
2)第二步创建一个触发器,向用户表中插入数据的时候触发触发器,在触发器内部调用序列并生成一个序列值赋值给表的id列。

触发器:

 create or replace trigger pro_id
  after insert on userinfo
  for each row
begin
  update userinfo set id=seq_id.nextval where username=:new.username;
end;

执行该语句时出错:

 insert into userinfo(username, userpass) values('李四',123456);

在行 1 上开始执行命令时出错:
insert into userinfo(username, userpass) values('李四',123456)
错误报告:
SQL 错误: ORA-04091: 表 SCOTT.USERINFO 发生了变化, 触发器/函数不能读它
ORA-06512: 在 "SCOTT.PRO_ID", line 2
ORA-04088: 触发器 'SCOTT.PRO_ID' 执行过程中出错
04091. 00000 - "table %s.%s is mutating, trigger/function may not see it"
*Cause: A trigger (or a user defined plsql function that is referenced in
this statement) attempted to look at (or modify) a table that was
in the middle of being modified by the statement which fired it.
*Action: Rewrite the trigger (or function) so it does not read that table.

请问哪里出错了?

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

1条回答 默认 最新

  • 已采纳
    baozhadehulu baozhadehulu 2016-05-29 13:39

    知道了,用法错误:不能用update语句,要用select...into...语句:
    create or replace
    trigger pro_id
    before insert on userinfo
    for each row
    begin
    select seq_id.nextval into :new.id from dual;
    end;

    点赞 评论 复制链接分享

相关推荐