hkyy-service/xms-core/db/触发器/oracle_delete.sql

--------------------------------------------------------
-- Export file for user XMS@LOCALHOST                 --
-- Created by Administrator on 2020/1/20 星期一, 9:40:11 --
--------------------------------------------------------

set define off
spool delete_oracle.log

prompt
prompt Creating trigger DELETE_ACCT
prompt ============================
prompt
create or replace trigger xms.delete_acct
  after delete on xms_user_acct
  for each row
declare
  -- local variables here
begin
  --删除关联表数据
  DELETE FROM xms_bind_item
   WHERE source = :old.acct_id
     AND SUBSTR(bind_type, 0, 4) = 'acct';
  DELETE FROM xms_bind_item
   WHERE target = :old.acct_id
     AND SUBSTR(bind_type, -4) = 'acct';
end delete_acct;
/

prompt
prompt Creating trigger DELETE_APP
prompt ===========================
prompt
create or replace trigger xms.delete_app
  after delete on xms_app_info
  for each row
declare
  -- local variables here
begin
  --1.删除资源关联
  DELETE FROM xms_app_res WHERE app_id = :old.app_id;
  --2.删除角色关联
  DELETE FROM xms_app_role WHERE app_id = :old.app_id;
  --删除关联表数据
  DELETE FROM xms_bind_item
   WHERE source = :old.app_id
     AND SUBSTR(bind_type, 0, 3) = 'app';
  DELETE FROM xms_bind_item
   WHERE target = :old.app_id
     AND SUBSTR(bind_type, -3) = 'app';
end delete_app;
/

prompt
prompt Creating trigger DELETE_CERT
prompt ============================
prompt
create or replace trigger xms.delete_cert
  after delete on xms_user_cert
  for each row
declare
  -- local variables here
begin
  --删除关联表数据
  DELETE FROM xms_bind_item
   WHERE source = :old.cert_id
     AND SUBSTR(bind_type, 0, 4) = 'cert';
  DELETE FROM xms_bind_item
   WHERE target = :old.cert_id
     AND SUBSTR(bind_type, -4) = 'cert';
end delete_cert;
/

prompt
prompt Creating trigger DELETE_GROUP
prompt =============================
prompt
create or replace trigger xms.delete_group
  after delete on xms_user_group
  for each row
declare
  -- local variables here
begin
  --删除关联表数据
  DELETE FROM xms_bind_item
   WHERE source = :old.group_id
     AND SUBSTR(bind_type, 0, 5) = 'group';
  DELETE FROM xms_bind_item
   WHERE target = :old.group_id
     AND SUBSTR(bind_type, -5) = 'group';
end delete_group;
/

prompt
prompt Creating trigger DELETE_ORG
prompt ===========================
prompt
create or replace trigger xms.delete_org
  after delete on xms_user_org
  for each row
declare
  -- local variables here
begin
  --删除关联表数据
  DELETE FROM xms_bind_item
   WHERE source = :old.org_id
     AND SUBSTR(bind_type, 0, 3) = 'org';
  DELETE FROM xms_bind_item
   WHERE target = :old.org_id
     AND SUBSTR(bind_type, -3) = 'org';
end delete_org;
/

prompt
prompt Creating trigger DELETE_RES
prompt ===========================
prompt
create or replace trigger xms.delete_res
  after delete on xms_app_res
  for each row
declare
  -- local variables here
begin
  --删除关联表数据
  DELETE FROM xms_bind_item
   WHERE source = :old.res_id
     AND SUBSTR(bind_type, 0, 3) = 'res';
  DELETE FROM xms_bind_item
   WHERE target = :old.res_id
     AND SUBSTR(bind_type, -3) = 'res';
end delete_res;
/

prompt
prompt Creating trigger DELETE_ROLE
prompt ============================
prompt
create or replace trigger xms.delete_role
  after delete on xms_app_role
  for each row
declare
  -- local variables here
begin
  --删除关联表数据
  DELETE FROM xms_bind_item
   WHERE source = :old.role_id
     AND SUBSTR(bind_type, 0, 4) = 'role';
  DELETE FROM xms_bind_item
   WHERE target = :old.role_id
     AND SUBSTR(bind_type, -4) = 'role';
end delete_role;
/

prompt
prompt Creating trigger DELETE_USER
prompt ============================
prompt
create or replace trigger xms.delete_user
  after delete on xms_user_info
  for each row
declare
  -- local variables here
begin
  --1.删除帐号关联
  DELETE FROM xms_user_acct WHERE user_id = :old.user_id;
  --2.删除证书关联
  DELETE FROM xms_user_cert WHERE user_id = :old.user_id;
  --3.删除凭证关联
  DELETE FROM xms_user_register_cred WHERE user_id = :old.user_id;
  --删除关联表数据
  DELETE FROM xms_bind_item
   WHERE source = :old.user_id
     AND SUBSTR(bind_type, 0, 4) = 'user';
  DELETE FROM xms_bind_item
   WHERE target = :old.user_id
     AND SUBSTR(bind_type, -4) = 'user';
end delete_user;
/


spool off