Question

oracle触发器的用法问题

小弟初学oracle，我想在在student上使用触发器，建表语句如下：
create table student
(
id number(5) primary key,
name varchar(10),
sex varchar(5),
score number(5) check(score>=0 and score<=100)
)
触发器中使用记录表类型，但是我使用select into到记录表时，老是报警告: 创建的触发器带有编译错误。是不是我的用法有错误呢，麻烦各位赐教！不甚感激
create or replace trigger addtrigger
before insert on student for each row
declare
type table_studentid is table of number
index by binary_integer;
table_sid table_studentid;
begin
select * into table_sid from student;
end;

Answer 1

你的触发器有两处错误。

1、你的 table_sid 是一个 number型的数组，因此你select * 是不可以的。
2、你想把你查询的结果直接写到数组里面，必须用 buik collect into的语法。

正确写法如下，虽然我不知道你select into 的目的。

[TEST@ora10gr1] SQL>create or replace trigger addtrigger
2 before insert on student for each row
3 declare
4 type table_studentid is table of number
5 index by binary_integer;
6 table_sid table_studentid;
7 begin
8 select id bulk collect into table_sid from student;
9 end;
10 /

触发器已创建

[TEST@ora10gr1] SQL>

---
以上，希望对你有所帮助。

Answer 2

create or replace trigger addtrigger
before insert on student for each row
declare
type table_studentid is table of number;
index by binary_integer;
table_sid table_studentid;
begin
insert into table_sid select * into from student;
end;

Answer 3

create or replace trigger addtrigger
before insert on student for each row
begin
insert into table_sid select * from student;
end;