注:模糊查询 拼接符一定是 || 而不是+符合
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;