触发器学习《二》 模糊查询 、增删改示例

注：模糊查询  拼接符一定是 || 而不是+符合
create or replace trigger user_trigger_pro
after insert or update or delete on kq_org_person
for each row
declare
    getid varchar(50); --自动增长列获取
    getorgid varchar(50); 
    newId varchar(50):=:new.id;--动态赋值
begin
   if deleting then
      delete from oa2_user@gjdb where us_ident=:old.id;
  end if;
  if inserting then
      select autoId.nextval into getid from dual;
      insert into kq_org_interchange(id,systemid,pfid,type) values((select sys_guid() from dual),:new.id,getid,'0');--人员信息录入中间表
      insert into oa2_user@gjdb(us_ident,us_name,us_code,us_pword)values(getid,:new.name,:new.login_name,:new.password);--录入平台人员表
      select org_id  into getorgid from kq_org_persons where person_id like newId||'%' and org_type='DEPARTMENT';   --注：模糊查询
      insert into kq_org_interchange(id,systemid,pfid,type) values((select sys_guid() from dual),getorgid,getid,'1');--部门信息录入中间表
      insert into oa2_oguser@gjdb(userid,og_ident)values(:new.id,getid);--录入平台部门表
  end if;
  if updating then
     update oa2_user@gjdb set us_name=:new.name,us_code=:new.login_name,us_pword=:new.password where us_ident=:old.id;
  end if;
end;

 增、删、改示例

测试
delete from test_user where user_id=5;
insert into test_user(user_id,user_name) values(5,'fff')
update test_user set user_name='test' where user_id=5

查看效果
select * from test_user;
select * from userinfo;


触发器编写
create or replace trigger test_user_trigger
    after insert or update or delete on test_user
    for each row
begin
   if deleting then
      delete from userinfo where id=:old.user_id;
  end if;
  if inserting then
      insert into userinfo(id,name)
      values(:new.user_id,:new.user_name);
  end if;
  if updating then
     update userinfo set name=:new.user_name where id=:old.user_id;
  end if;
end;