試一下create trigger trg1 before insert or update on a4 for each row begin if :new.status = 2 then insert into user_b.table_b select a2.* from a2,a3,a4 where a2.deal_plan_step_id = a3.deal_plan_step_id and a3.deal_plan_order_unit_id = a4.deal_plan_order_unit_id and a4.public_issue_plan_order_id = :new.public_issue_plan_order_id; end if; end;
在status改为2时,提示insert into user_b.table_b 这一行触发器/函数不能读它? 已经按楼上说的在userb下执行 grant all on b1 to usera
sorry,這種方法在update時會產生ORA-04091: table xxx is mutating, trigger/function may not see it。 需要另外一些技巧
-- 我的用戶a是hr,下面是測試表和數據 create table a2 ( id_t2 number primary key, name varchar2(20) );create table a3 ( id_t3 number primary key, id_t2 number, constraint fk_a2 foreign key (id_t2) references a2(id_t2));create table a4 ( id_t4 number primary key, id_t3 number, status char(1), constraint fk_a3 foreign key (id_t3) references a3(id_t3));insert into a2 values (1, 'a'); insert into a2 values (2, 'b');insert into a3 values (1, 1); insert into a3 values (2, 1); insert into a3 values (3, 2);insert into a4 values (1, 1, '1'); insert into a4 values (2, 2, '1'); insert into a4 values (3, 3, '1');commit;-- 用戶b是scott create table b ( id_t2 number primary key, name varchar2(20) );grant all on b to hr;-- 在用戶a下創建一個package和兩個trigger create or replace package pkg1 as type tab_id_t4 is table of a4.id_t4%type index by binary_integer; id_t4s tab_id_t4; v_num_ent binary_integer := 0; end;create or replace trigger trg1 before insert or update on a4 for each row begin dbms_output.put_line(pkg1.v_num_ent); if :new.status = '2' then pkg1.v_num_ent := pkg1.v_num_ent + 1; pkg1.id_t4s(pkg1.v_num_ent) := :new.id_t4; end if; end;create or replace trigger trg2 after insert or update on a4 begin for idx in 1..pkg1.v_num_ent loop dbms_output.put_line(pkg1.id_t4s(idx)); insert into scott.b select a2.* from a2,a3,a4 where a2.id_t2 = a3.id_t2 and a3.id_t3 = a4.id_t3 and a4.id_t4 = pkg1.id_t4s(idx); end loop;
pkg1.v_num_ent := 0;
exception when others then pkg1.v_num_ent := 0; dbms_output.put_line(sqlerrm); end;-- 測試 update a4 set status = '2' where id_t4 = 1;-- 在用戶b下檢驗結果 select * from b;-- 結果 ID_T2 NAME ---------- -------------------- 1 a
字段名称 外键 主键 说明
deal_plan_step_id PK
deal_plan_id TK_DealPlan.DealPLanID
step_no
step_desc
表A3:
字段名称 外键 主键 说明
deal_plan_order_unit_id PK
deal_plan_step_id A2.DealPlanStepID
order_unit_desc
plan_action_unit_type_id
表A4:
字段名称 外键 主键 说明
public_issue_plan_order_id PK
deal_plan_order_unit_id A3.DealPlanOrderUnitID
record_type
message_content
create_time
status 可能取值为1,2,3,4
为了能看清楚表结构,空格用代替表A2:
字段名称 。。外键 。主键。 说明
deal_plan_step_id PK
deal_plan_id。TK_DealPlan.DealPLanID
step_no
step_desc
表A3:
字段名称 。外键 。。主键 说明
deal_plan_order_unit_id PK
deal_plan_step_id 。A2.DealPlanStepID
order_unit_desc
plan_action_unit_type_id
表A4:
字段名称 外键 主键 说明
public_issue_plan_order_id。 PK
deal_plan_order_unit_id 。A3.DealPlanOrderUnitID
record_type
message_content
create_time
status 可能取值为1,2,3,4
before insert or update on a4
for each row
begin
if :new.status = 2 then
insert into user_b.table_b
select a2.* from a2,a3,a4
where a2.deal_plan_step_id = a3.deal_plan_step_id
and a3.deal_plan_order_unit_id = a4.deal_plan_order_unit_id
and a4.public_issue_plan_order_id = :new.public_issue_plan_order_id;
end if;
end;
把B1的insert权限赋予用户A
已经按楼上说的在userb下执行 grant all on b1 to usera
需要另外一些技巧
-- 我的用戶a是hr,下面是測試表和數據
create table a2 (
id_t2 number primary key,
name varchar2(20)
);create table a3 (
id_t3 number primary key,
id_t2 number,
constraint fk_a2 foreign key (id_t2) references a2(id_t2));create table a4 (
id_t4 number primary key,
id_t3 number,
status char(1),
constraint fk_a3 foreign key (id_t3) references a3(id_t3));insert into a2 values (1, 'a');
insert into a2 values (2, 'b');insert into a3 values (1, 1);
insert into a3 values (2, 1);
insert into a3 values (3, 2);insert into a4 values (1, 1, '1');
insert into a4 values (2, 2, '1');
insert into a4 values (3, 3, '1');commit;-- 用戶b是scott
create table b (
id_t2 number primary key,
name varchar2(20)
);grant all on b to hr;-- 在用戶a下創建一個package和兩個trigger
create or replace package pkg1 as
type tab_id_t4 is table of a4.id_t4%type index by binary_integer;
id_t4s tab_id_t4;
v_num_ent binary_integer := 0;
end;create or replace trigger trg1
before insert or update on a4
for each row
begin
dbms_output.put_line(pkg1.v_num_ent);
if :new.status = '2' then
pkg1.v_num_ent := pkg1.v_num_ent + 1;
pkg1.id_t4s(pkg1.v_num_ent) := :new.id_t4;
end if;
end;create or replace trigger trg2
after insert or update on a4
begin
for idx in 1..pkg1.v_num_ent loop
dbms_output.put_line(pkg1.id_t4s(idx));
insert into scott.b
select a2.* from a2,a3,a4
where a2.id_t2 = a3.id_t2
and a3.id_t3 = a4.id_t3
and a4.id_t4 = pkg1.id_t4s(idx);
end loop;
pkg1.v_num_ent := 0;
exception
when others then
pkg1.v_num_ent := 0;
dbms_output.put_line(sqlerrm);
end;-- 測試
update a4 set status = '2' where id_t4 = 1;-- 在用戶b下檢驗結果
select * from b;-- 結果
ID_T2 NAME
---------- --------------------
1 a