有两个表,如下
Table1字段如下:
code 编码
p_code 父级编码,Table1中的数据有父子关系,通过此字段来表示
caption 名称
Table2 字段如下
Id 需要自增
t1_code 对应Table1里的code
t1_pcode 对应Table1中的p_code
caption 对应Table1 中的caption
需要完成如下的功能
1、当修改Table1里的code时,更新它的子级数据的p_code
2、当删除Table1里一条数据时,删除该数据的子级数据
3、Table1中数据的增、删、改都要同步到Table2中谢谢!
Table1字段如下:
code 编码
p_code 父级编码,Table1中的数据有父子关系,通过此字段来表示
caption 名称
Table2 字段如下
Id 需要自增
t1_code 对应Table1里的code
t1_pcode 对应Table1中的p_code
caption 对应Table1 中的caption
需要完成如下的功能
1、当修改Table1里的code时,更新它的子级数据的p_code
2、当删除Table1里一条数据时,删除该数据的子级数据
3、Table1中数据的增、删、改都要同步到Table2中谢谢!
code number,-- 编码
p_code number,--父级编码,Table1中的数据有父子关系,通过此字段来表示
caption varchar2(30)--名称
)
create sequence seq_table2;--创建序列create table table2(
Id number,--需要自增
t1_code number,--对应Table1里的code
t1_pcode number,--对应Table1中的p_code
caption varchar2(30) -- 对应Table1 中的caption
)
create or replace trigger tri_test--创建触发器
after insert or update or delete
on table1
for each row--触发每一行
DECLARE v_seq NUMBER; --申明序列
begin
if inserting then
select seq_table2.nextval into v_seq from dual;
insert into table2 values(v_seq,:new.code,:new.p_code,:new.caption);
dbms_output.put_line('插入');
elsif deleting then
dbms_output.put_line('删除');
delete from table2 t2 where t2.t1_code=:new.code and t2.t1_pcode=:new.code;
elsif updating then
update table2 t2 set t2.t1_code=:new.code ,t2.t1_pcode=:new.code ,t2.caption=:new.caption;
dbms_output.put_line('更新');
end if;exception
when others then
raise_application_error(-20020,'触发器错误!');
commit;
end;
SQL> insert into table1 values(1001,1001,'yang');1 row insertedSQL> commit;Commit completeSQL> select * from table1; CODE P_CODE CAPTION
---------- ---------- ------------------------------
1001 1001 yangSQL> select * from table2; ID T1_CODE T1_PCODE CAPTION
---------- ---------- ---------- ------------------------------
1 1001 1001 yang
elsif updating then
update table2 t2 set t2.t1_code=:new.code ,t2.t1_pcode=:new.code ,t2.caption=:new.caption;
dbms_output.put_line('更新');
end if;改成elsif updating then
update table2 t2 set t2.t1_pcode=:new.code ,t2.caption=:new.caption where t2.t1_code=:new.code ,;
dbms_output.put_line('更新');
end if;
begin
if inserting then
insert into table2(t1_code,t1_pcode,caption) values(:new.code,:new.p_code,:new.caption);
elsif updating then
update table2 set t1_code=:new.code,t1_pcode=:new.p_code,caption=:new.caption;
elsif deleting then
delete from table2 where code=old.code;
end if;
end;
after insert or delete or update on t1
for each row
declare
begin
if inserting then
insert into t2 values(:new.code,:new.p_code,:new.captiion);
elsif updating then
update t2 set t1_pcode = :new.code where t1_pcode = :old.code;
update t2 set t1_code = :new.code where t1_code = :old.code;
elsif deleting then
delete from t2 where t1_code in(select t1_code from t2 start with t1_code = :old.code
connect by prior t1_code = t1_pcode);
end if;
exception when others then
raise_application_error(-20020,'触发器错误!');
commit;
end tt;
另外写几个procedures来循环更新和删除table1和table2中的子阶
在trigger里去调用这些procedurescreate or replace trigger tri_table1--创建触发器
after insert or update or delete
on table1
for each row--触发每一行
declare
v_seq number; --申明序列
begin
if inserting then
dbms_output.put_line('插入');
select seq_table2.nextval into v_seq from dual;
insert into table2 values(v_seq,:new.code,:new.p_code,:new.caption);
elsif deleting then
dbms_output.put_line('删除');
delete from table2 t2 where t2.t1_code=:old.code and t2.t1_pcode=:old.p_code;
delete_table1(:old.code);--调用procedure,删除table1表中code对应的子阶
delete_table2(:old.code);--调用procedure,删除table2表中code对应的子阶
elsif updating then
dbms_output.put_line('更新');
update table2 set t1_code=:new.code ,t1_pcode=:new.p_code ,t2.caption=:new.caption;
update_table1(:new.code,:old.code); --调用procedure,更新table1表中code对应的子阶
update_table2(:new.code,:old.code); --调用procedure,更新table2表中code对应的子阶
end if;
exception
when others then
raise_application_error(-20020,'触发器错误!');
end;