触发器的错误，找了半天没发现？急

[ruanwxh]

create or replace trigger tgr_bulletin_add
  after insert on bulletin
  for each row
declare
  -- local variables here
  i_count      number(10);
begin
  delete from bulletin_to_user where bulletin_id=:new.id_num;
  for i_count in (select id from sys_user) loop
      insert into bulletin_to_user(id_num,bulletin_id,user_id,readed) values(bulletin_to_user_seq.nextval,:new.id_num,i_count,0);
  end loop;
end tgr_bulletin_add;

求高手帮忙看下这个触发器哪里有错？
提示
LINE/COL ERROR
-------- -------------------------
7/119 PLS-00382: 表达式类型错误

[Phoenix_99]

建议还是先看一下各个对应的类型是否正确，然后(select id from sys_user) 改为常量，再试一下

[ruanwxh]

select id from sys_user我改成select 1 from dual也是错的。
而
insert into bulletin_to_user(id_num,bulletin_id,user_id,readed) values(bulletin_to_user_seq.nextval,:new.id_num,i_count,0);i_count改成1就没报错.

[ORHCLE]

for i_count in (select id from sys_user) loop
      insert into bulletin_to_user(id_num,bulletin_id,user_id,readed) values(bulletin_to_user_seq.nextval,:new.id_num,i_count.id,0);
  end loop;

这样子了

[Phoenix_99]

把(select id from sys_user)改成10呢

[ruanwxh]

[Quote=引用 4 楼 phoenix_99 的回复:]
把(select id from sys_user)改成10呢
[/Quote]
改成10也不行

[ruanwxh]

[Quote=引用 3 楼 orhcle 的回复:]
SQL code
for i_count in (select id from sys_user) loop
insert into bulletin_to_user(id_num,bulletin_id,user_id,readed) values(bulletin_to_user_seq.nextval,:new.id_num,i_count.id,0);
end loo……
[/Quote]
为什么改成i_count.id就可以了。i_count我不是声明为number(10)呢?

[fxianxian]

把declare
-- local variables here
i_count number(10);
去掉
这段是多余的

[Phoenix_99]

你的i_count也没有附初值啊,也没有增值

[ORHCLE]

[Quote=引用 6 楼 ruanwxh 的回复:]
引用 3 楼 orhcle 的回复:
SQL code
for i_count in (select id from sys_user) loop
insert into bulletin_to_user(id_num,bulletin_id,user_id,readed) values(bulletin_to_user_seq.nextval,:new.id_num,i_count.id,……
[/Quote]
此处调用的是游标，它是可见的对这块。

[Raul_Gonzalez]

CREATE OR REPLACE TRIGGER tgr_bulletin_add
   AFTER INSERT
   ON bulletin
   FOR EACH ROW
DECLARE
   -- local variables here
   i_count   NUMBER (10);  --这里是声明变量
BEGIN
   DELETE FROM bulletin_to_user
         WHERE bulletin_id = :NEW.id_num;

   FOR rec IN (SELECT ID                --这里是游标结果集
                     FROM sys_user)
   LOOP
      INSERT INTO bulletin_to_user
                  (id_num, bulletin_id, user_id, readed
                  )
           VALUES (bulletin_to_user_seq.NEXTVAL, :NEW.id_num, rec.id, 0
                  );
   END LOOP;
END tgr_bulletin_add;

[fxianxian]

values(bulletin_to_user_seq.nextval,:new.id_num,i_count.id,0);
这个你要好好看看游标的用法啊

[ruanwxh]

多谢谢上面的高手们
分不多，大家见谅了.