table a 從表empno orderno
F1 001
F2 002
F3 002
那么table b 主表的數據應該為
------------------------------
empno orderno
F1 001
F2,F3 002即table a 記錄每個員工empno分別做了什么orderno,
table b記錄每個工作orderno 由哪些人完成。舉例﹕
當table a insert F4 完成002﹐F2員工換成F5時﹐
empno orderno
F1 001
F5 002
F3 002
F4 002
-----------------------------
table b 的記錄應該為
empno orderno
F1 001
F5,F3﹐F4 002請幫忙﹐謝謝﹗﹗﹗﹗謝謝﹗
F1 001
F2 002
F3 002
那么table b 主表的數據應該為
------------------------------
empno orderno
F1 001
F2,F3 002即table a 記錄每個員工empno分別做了什么orderno,
table b記錄每個工作orderno 由哪些人完成。舉例﹕
當table a insert F4 完成002﹐F2員工換成F5時﹐
empno orderno
F1 001
F5 002
F3 002
F4 002
-----------------------------
table b 的記錄應該為
empno orderno
F1 001
F5,F3﹐F4 002請幫忙﹐謝謝﹗﹗﹗﹗謝謝﹗
BEFORE
INSERT OR UPDATE
ON A
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
begin
If updating then
update b set empno=:new.empno,orderno=:new.orderno where empno=:old.empno;
If inserting then
intert into b values(:new.empno,:new.orderno);
exception when others then rollback;
end;
BEFORE INSERT OR UPDATE ON A
FOR EACH ROW
BEGIN
UPDATE b SET empno = empno || ',' || :new.empno WHERE orderno = :new.orderno;
IF SQL%NOTFOUND THEN
INSERT INTO b VALUES (:new.empno, :new.orderno);
END IF;
END;
/
CREATE OR REPLACE TRIGGER TR_A
BEFORE INSERT OR UPDATE OR DELETE ON A
FOR EACH ROW
BEGIN
IF inserting OR updating THEN
UPDATE b SET empno = empno || ',' || :new.empno WHERE orderno = :new.orderno;
IF SQL%NOTFOUND THEN
INSERT INTO b VALUES (:new.empno, :new.orderno);
END IF;
ELSIF deleting THEN
DELETE FROM b
WHERE b.orderno = :old.orderno AND
b.empno = :old.empno;
IF SQL%NOTFOUND THEN
UPDATE b
SET empno = rtrim(ltrim(REPLACE(','||empno||',', ','||:old.empno||',', ','), ','), ',')
WHERE orderno = :old.orderno;
END IF;
END IF;
END;
/
for each row
begin
if inserting then
update b set empno=empno||','||:new.empno where orderno=:new.orderno;
elsif updating then
update a set empno=replace(empno,:old.empno,:new.empno) where orderno=:new.orderno;
elsif deleting then
update a set empno=replace(empno,:old.empno||',','') where orderno=:new.orderno;
end if;
end;
SQL> create or replace trigger tri_ftbb before insert or delete or update on a
2 for each row
3 begin
4 if inserting then
5 update b set empno=empno||','||:new.empno where orderno=:new.orderno;
6 elsif updating then
7 update a set empno=replace(empno,:old.empno,:new.empno) where orderno=:new.orderno;
8 elsif deleting then
9 update a set empno=replace(empno,:old.empno||',','') where orderno=:new.orderno;
10 end if;
11 end;
12 /触发器已创建SQL> update a set empno='F5' where orderno='001';
update a set empno='F5' where orderno='001'
*
第 1 行出现错误:
ORA-04091: 表 SCOTT.A 发生了变化, 触发器/函数不能读它
ORA-06512: 在 "SCOTT.TRI_FTBB", line 5
ORA-04088: 触发器 'SCOTT.TRI_FTBB' 执行过程中出错
for each row
declare
num number;
begin
if inserting then
select count(*) into num from b where orderno=:new.orderno;;
if num>0 then
update b set empno=empno||','||:new.empno where orderno=:new.orderno;
else
insert into b values(:new.empno,:new.orderno);
end if;
elsif updating then
update a set empno=replace(empno,:old.empno,:new.empno) where orderno=:new.orderno;
elsif deleting then
update a set empno=replace(empno,:old.empno||',','') where orderno=:new.orderno;
end if;
end;
create or replace trigger tri_ftbb before insert or delete or update on a
for each row
declare
num number;
begin
if inserting then
select count(*) into num from b where orderno=:new.orderno;;
if num>0 then
update b set empno=empno||','||:new.empno where orderno=:new.orderno;
else
insert into b values(:new.empno,:new.orderno);
end if;
elsif updating then
update b set empno=replace(empno,:old.empno,:new.empno) where orderno=:new.orderno;
elsif deleting then
update b set empno=replace(empno,:old.empno||',','') where orderno=:new.orderno;
end if;
end;
SQL> select * from a;EM ORD
-- ---
F5 001
F2 002
F3 002SQL> select * from b;EMPNO
----------------------------------------------------
ORDERNO
--------------------
F5
001F2,F3
002
SQL> update a set empno='F1' where orderno='001';已更新 1 行。SQL> commit;提交完成。SQL> select * from b;EMPNO
----------------------------------------------------
ORDERNO
--------------------
F5,F1
001F2,F3
002
SQL> select * from a;EM ORD
-- ---
F1 001
F2 002
F3 002
--终结create or replace trigger tri_ftbb before insert or delete or update on a
for each row
declare
num number;
begin
if inserting then
select count(*) into num from b where orderno=:new.orderno;;
if num>0 then
update b set empno=empno||','||:new.empno where orderno=:new.orderno;
else
insert into b values(:new.empno,:new.orderno);
end if;
elsif updating then
update b set empno=replace(empno,:old.empno,:new.empno) where orderno=:new.orderno;
elsif deleting then
update b set empno=replace(empno,:old.empno||',','') where orderno=:old.orderno;
end if;
end;
SQL> delete a where empno='F5' and orderno='003';已删除 1 行。SQL> commit;提交完成。SQL> select * from a;EM ORD
-- ---
F4 001
F2 002
F3 002
F6 003SQL> select * from b;EMPNO
-----------------------------------------------------------
ORDERNO
--------------------
F4
001F5,F6
003F2,F3
002
可以用循環來做嗎﹖當table a 數據變化時﹐
by order把所有empno 存在一個變量里﹐用逗號隔開。然后再update 到主表table b 里面﹖
不知道這樣能否實現﹐請各位幫忙 ﹐謝謝﹗謝謝﹗﹗﹗
select count(*) into num from b where orderno=:new.orderno;;
就这一句多了个分号
從表a中﹐數據如下﹕
empno orderno item
F1 001 1
F2 002 1
F3 002 2但是主表B中﹐實際數據卻是
empno orderno
F1,F2 001
F5,F6,F9 002
如果執行update a set empno=F4 where empno=F2
那么trigger運行到
update b set empno=replace(empno,:old.empno,:new.empno) where orderno=:new.orderno;
也就是
update b set empno = replace('F5,F6,F9','F2','F4')where orderno = '002'
就不能replace.
所以說效果不好。原因是我們的數據問題。然后再問一下﹕
可以用循環來做嗎﹖當table a 數據變化時﹐
by order把所有empno 存在一個變量里﹐用逗號隔開。然后再update 到主表table b 里面﹖
不知道這樣能否實現﹐請各位幫忙 ﹐謝謝﹗謝謝﹗﹗﹗
create or replace trigger tri_ftbb before insert or delete or update on a
for each row
declare
num number;
num2 number;
begin
if inserting then
select count(*) into num from b where orderno=:new.orderno;
if num>0 then
update b set empno=empno||','||:new.empno where orderno=:new.orderno;
else
insert into b values(:new.empno,:new.orderno);
end if;
elsif updating then
select count(*) into num2 from b
where orderno=:new.orderno and instr(empno,:old.empno,1)>0
if num2>0 then
update b set empno=replace(empno,:old.empno,:new.empno) where orderno=:new.orderno;
else
update b set empno=empno||','||:new.empno where orderno=:new.orderno;
end if;
elsif deleting then
update b set empno=replace(empno,:old.empno||',','') where orderno=:old.orderno;
end if;
end;
想你说的 循环 性能不好 建议干脆用过程调用
create or replace procedure proc_b
as
begin
for i in(select orderno,wm_concat(empno) empno from a group by orderno)
loop
update a set a.empno=i.empno
where a.orderno=b.orderno;
commit;
end loop;
exception
when others then
dbms_output.put_line(sqlcode||' '||sqlerrm);
end;
for each row
declare
num number;
num2 number;
begin
if inserting then
select count(*) into num from b where orderno=:new.orderno;
if num>0 then
update b set empno=empno||','||:new.empno where orderno=:new.orderno;
else
insert into b values(:new.empno,:new.orderno);
end if;
elsif updating then
select count(*) into num2 from b
where orderno=:new.orderno and instr(empno,:old.empno,1)>0;
if num2>0 then
update b set empno=replace(empno,:old.empno,:new.empno) where orderno=:new.orderno;
else
update b set empno=empno||','||:new.empno where orderno=:new.orderno;
end if;
elsif deleting then
update b set empno=replace(empno,:old.empno||',','') where orderno=:old.orderno;
end if;
end;
那我豈不在界面程序中﹐insert調用一次﹐update也要調用﹐delete還要調用﹖﹖﹖
create or replace trigger Range_a_b
before insert or update or delete on a for each row
begin
if inserting or updating then
update b set (empno,orderno)=(select wm_concat(empno),orderno from a
where orderno = :new.orderno group by orderno);
if SQL%notfound then
insert into b select wm_concat(:new.empno)over(order by :new.orderno), :new.orderno from dual;
end if;
end if;
end;