create or replace trigger trg_test after insert on A as max_xuhao number; begin select max(xuhao) into max_xuhao from B; insert into B values(max_xuhao+1,:new.name,:new.address); end;
应该是这样 create or replace trigger trg_test after insert on A declare max_xuhao number; begin select max(xuhao) into max_xuhao from B; insert into B values(max_xuhao+1,:new.name,:new.address); end;
create or replace trigger trg_test after insert on A for each row declare max_xuhao number; num number; begin select max_xuhao into num from b where name=:new.name and address=:new.address; insert into B values(max_xuhao+1,:new.name,:new.address); exception when others then select max(xuhao) into max_xuhao from B; insert into B values(max_xuhao+1,:new.name,:new.address); end; /
create or replace trigger trig_test before insert on A referencing old as old new as new for each row declare max_id number; begin select max(xuhao) into max_id from B; insert into B values(max_id+1,:new.name,:new.address); end;
create or replace trigger trig_test before insert on A for each row declare max_id number; begin select nvl(max(xuhao),0) into max_id from B; insert into B values(max_id+1,:new.name,:new.address); end;
如果我想在查找xuhao最大值时添加条件来查找呢? 如何写?select max(xuhao) into max_id from B where……?
CREATE OR REPLACE TRIGGER SYSTEM.AINSERT BEFORE INSERT ON SYSTEM.A REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW declare v_xuhao number(5); BEGIN select max(xuhao) into v_xuhao from b; if v_xuhao is null then insert into b values(1,:new.name,:new.address); elseinsert into b values(v_xuhao+1,:new.name,:new.address); END if; end; /
谢谢 mayali() 解决了我心中一个疑问不过还有个问题,如果A表里面有一个字段比如说ID,是用触发器生成的。那么上面提到的触发器里如何引用这个ID给B表的ID赋值呢。我直接用 new.ID 好像不行,不能这样访问。 而我采用 select max(ID) into num from A; 然后在赋值给B,也不行。据报告是因为表里面数据正在变动,所以无法查出最大值。 但我这个触发器已经设为A表插入数据后运行了。不知道我还有什么地方没设好?
create or replace trigger trg_test
after insert on A
declare
max_xuhao number;
begin
select max(xuhao) into max_xuhao from B;
insert into B values(max_xuhao+1,:new.name,:new.address);
end;
after insert on A
for each row
declare
max_xuhao number;
num number;
begin
select max_xuhao into num from b where name=:new.name and address=:new.address;
insert into B values(max_xuhao+1,:new.name,:new.address);
exception
when others then
select max(xuhao) into max_xuhao from B;
insert into B values(max_xuhao+1,:new.name,:new.address);
end;
/
declare
max_id number;
begin
select max(xuhao) into max_id from B;
insert into B values(max_id+1,:new.name,:new.address);
end;
before insert on A
for each row
declare
max_id number;
begin
select nvl(max(xuhao),0) into max_id from B;
insert into B values(max_id+1,:new.name,:new.address);
end;
如何写?select max(xuhao) into max_id from B where……?
解释清楚:
每向A表插入一条数据,也要向B表插入一条name和address值一样的数据,但xuhao要取当前B表中的xuhao的最大值加一执行那个触发器产生错误?
BEFORE INSERT
ON SYSTEM.A
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
declare
v_xuhao number(5);
BEGIN
select max(xuhao) into v_xuhao from b;
if v_xuhao is null then
insert into b values(1,:new.name,:new.address);
elseinsert into b values(v_xuhao+1,:new.name,:new.address);
END if;
end;
/
而我采用 select max(ID) into num from A; 然后在赋值给B,也不行。据报告是因为表里面数据正在变动,所以无法查出最大值。
但我这个触发器已经设为A表插入数据后运行了。不知道我还有什么地方没设好?