oracle 触发器

 当一张表插入数据的时候，如果权限涉及到另外的两个表，就把相对于的用户数据插入到另外的两个表中，以下是这个触发器的具体实现，功能还不够完善，可以考虑对插入数据如果重复添加判断，可以将异常抛出。

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
//测试代码