有个要求,当往一个表中输入数据的时候,触发器自动的创建和加入数据对应的用户。我开始时在触发器中创建用户的,但是因为不能在触发器中有commit或者rollback之类的动作,所以把创建用户的动作写为了一个存储过程,但是,还是有问题,提示权限不够。各位有明白的给个答案啊。很着急的。
附代码:
触发器的代码:
create or replace trigger INST_OCT_ACC_PRSN_TR
after insert or update or delete on oct_acc_prsn
for each row
declare
instID varchar2(50);
sqlText1 varchar2(200);
sqlText2 varchar2(200);
begin
if inserting then
instID := trim(:new.prsn_lg_id);
if instID IS NOT NULL THEN
OCT_ACC_PRSN_PR(instID,instID);
end if;
end if;
end INST_OCT_ACC_PRSN_TR;
存储过程的代码:
create or replace procedure OCT_ACC_PRSN_PR(userName varchar2, userPW varchar2) authid current_user is pragma autonomous_transaction;
begin
declare
sqlText1 varchar2(200);
sqlText2 varchar2(200);
begin
sqlText1 := 'create user ' || userName || ' identified by ' || userPW ;
sqlText2 := 'grant connect to ' || userName;
execute immediate sqlText1;
execute immediate sqlText2;
end;
end OCT_ACC_PRSN_PR;
附代码:
触发器的代码:
create or replace trigger INST_OCT_ACC_PRSN_TR
after insert or update or delete on oct_acc_prsn
for each row
declare
instID varchar2(50);
sqlText1 varchar2(200);
sqlText2 varchar2(200);
begin
if inserting then
instID := trim(:new.prsn_lg_id);
if instID IS NOT NULL THEN
OCT_ACC_PRSN_PR(instID,instID);
end if;
end if;
end INST_OCT_ACC_PRSN_TR;
存储过程的代码:
create or replace procedure OCT_ACC_PRSN_PR(userName varchar2, userPW varchar2) authid current_user is pragma autonomous_transaction;
begin
declare
sqlText1 varchar2(200);
sqlText2 varchar2(200);
begin
sqlText1 := 'create user ' || userName || ' identified by ' || userPW ;
sqlText2 := 'grant connect to ' || userName;
execute immediate sqlText1;
execute immediate sqlText2;
end;
end OCT_ACC_PRSN_PR;
------------------------
请详细说明一下。谢谢了。