当一张表插入数据的时候,如果权限涉及到另外的两个表,就把相对于的用户数据插入到另外的两个表中,以下是这个触发器的具体实现,功能还不够完善,可以考虑对插入数据如果重复添加判断,可以将异常抛出。
grant select,delete,update,select on a.tablename to b
//不同用户间 提权
select * from user_triggers
//查询所有的触发器
drop trigger tr_role_funciton;
//删除触发器
create or replace trigger tr_role_funciton
after insert on TD_SM_ROLERESOP
for each row
declare
v_exist_count INTEGER := 0;//赋值的时候注意:
b_exist_count INTEGER := 0;
u_user_id char(20);
u_name char(20);
u_pwd char(20);
u_idcard char(20);
zhcx_roleId INTEGER := 1;
jsbd_roleId INTEGER := 2;
t_id INTEGER := 0;
begin
select user_id into u_user_id
from td_sm_userrole
where role_id = :new.role_id; //与插入数据对比
select nvl(max(t.ID),0)+1 into t_id from jsbd.m_user t;//对查询值的运算
select user_name into u_name from td_sm_user where user_id = (select user_id
from td_sm_userrole where role_id = :new.role_id);
//select 中包含多个值的时候会报错,具体不清楚
select user_password into u_pwd from td_sm_user where user_id = (select user_id
from td_sm_userrole where role_id = :new.role_id);
select user_idcard into u_idcard from td_sm_user where user_id = (select user_id
from td_sm_userrole where role_id = :new.role_id);
if :new.res_id = 'zhcx' then
insert into u_zhcx.tb_m_user
(REPORTUSER_ID, NAME, PASSWORD)
values
(u_user_id, u_name, u_pwd);
insert into u_zhcx.tb_m_user_role_map
(USER_ID,ROLE_ID,MAP_ID)
values
(u_user_id,:new.role_id,0);//值为number 类型时 不用‘:’
elsif :new.res_id = 'plbd' then
insert into jsbd.m_user
(ID, xm, dwmc, ROLE_ID, USERNAME, USERPWD, isonline, ISADMIN, ISVALIDATE, NODEL, dwbm,IDCARD)
values
( t_id,'xxx', 'xxx', :new.role_id, u_name, u_pwd,
0, 0, 1, 1, 'xxx', u_idcard);
end if;
END;
insert into TD_SM_ROLERESOP
(OP_ID, RES_ID, ROLE_ID, RESTYPE_ID, RES_NAME, TYPES, ORG_ID)
values
('visible', 'plbd', '2', 'column', '批量比对', 'role', '-1')
delete td_sm_roleresop where role_id = 2
//测试代码