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

use xms;
SET SQL_SAFE_UPDATES = 0;
DELIMITER $$

/* Trigger structure for tablegaf_user_acct `xms_app_info` */
DROP TRIGGER IF EXISTS `delete_app` $$

CREATE TRIGGER `delete_app` AFTER DELETE ON `xms_app_info` FOR EACH ROW 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 SUBSTRING_INDEX(bind_type,'-',1) = 'app';		
	DELETE FROM  xms_bind_item
	WHERE target = old.app_id
		AND SUBSTRING_INDEX(bind_type,'-',-1) = 'app';		
    END $$
    
/* Trigger structure for table `xms_app_res` */    
DROP TRIGGER IF EXISTS `delete_res`$$

CREATE TRIGGER `delete_res` AFTER DELETE ON `xms_app_res` FOR EACH ROW BEGIN
	/*删除多对多关系*/
	DELETE FROM  xms_bind_item
	WHERE source = old.res_id
		AND SUBSTRING_INDEX(bind_type,'-',1) = 'res';
	DELETE FROM  xms_bind_item
	WHERE target = old.res_id
		AND SUBSTRING_INDEX(bind_type,'-',-1) = 'res';
    END $$
    
/* Trigger structure for table `xms_app_role` */
DROP TRIGGER IF EXISTS `delete_role` $$

CREATE TRIGGER `delete_role` AFTER DELETE ON `xms_app_role` FOR EACH ROW BEGIN
	/*删除多对多关系*/
	DELETE FROM  xms_bind_item
	WHERE source = old.role_id
		AND SUBSTRING_INDEX(bind_type,'-',1) = 'role';
	DELETE FROM  xms_bind_item
	WHERE target = old.role_id
		AND SUBSTRING_INDEX(bind_type,'-',-1) = 'role';
    END $$

/* Trigger structure for table `xms_user_acct` */
DROP TRIGGER IF EXISTS `delete_acct` $$

CREATE TRIGGER `delete_acct` AFTER DELETE ON `xms_user_acct` FOR EACH ROW BEGIN
	/*删除多对多关系*/
	DELETE FROM  xms_bind_item
	WHERE source = old.acct_id
		and SUBSTRING_INDEX(bind_type,'-',1) = 'acct';
	DELETE FROM  xms_bind_item
	WHERE target = old.acct_id
		AND SUBSTRING_INDEX(bind_type,'-',-1) = 'acct';
    END $$

/* Trigger structure for table `xms_user_cert` */
DROP TRIGGER IF EXISTS `delete_cert` $$

CREATE TRIGGER `delete_cert` AFTER DELETE ON `xms_user_cert` FOR EACH ROW BEGIN
	/*删除多对多关系*/
	DELETE FROM  xms_bind_item
	WHERE source = old.cert_id
		AND SUBSTRING_INDEX(bind_type,'-',1) = 'cert';
	DELETE FROM  xms_bind_item
	WHERE target = old.cert_id
		AND SUBSTRING_INDEX(bind_type,'-',-1) = 'cert';
    END $$

/* Trigger structure for table `xms_user_group` */
DROP TRIGGER IF EXISTS `delete_group` $$

CREATE TRIGGER `delete_group` AFTER DELETE ON `xms_user_group` FOR EACH ROW BEGIN
	/*删除多对多关系*/
	DELETE FROM  xms_bind_item
	WHERE source = old.group_id
		AND SUBSTRING_INDEX(bind_type,'-',1) = 'group';
	DELETE FROM  xms_bind_item
	WHERE target = old.group_id
		AND SUBSTRING_INDEX(bind_type,'-',-1) = 'group';
    END $$

/* Trigger structure for table `xms_user_info` */
DROP TRIGGER IF EXISTS `delete_user` $$

CREATE TRIGGER `delete_user` AFTER DELETE ON `xms_user_info` FOR EACH ROW 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 SUBSTRING_INDEX(bind_type,'-',1) = 'user';
	DELETE FROM  xms_bind_item
	WHERE target = old.user_id
		AND SUBSTRING_INDEX(bind_type,'-',-1) = 'user';
    END $$

/* Trigger structure for table `xms_user_org` */
DROP TRIGGER IF EXISTS `delete_org` $$

CREATE TRIGGER `delete_org` AFTER DELETE ON `xms_user_org` FOR EACH ROW BEGIN
	/*删除多对多关系*/
	DELETE FROM  xms_bind_item
	WHERE source = old.org_id
		AND SUBSTRING_INDEX(bind_type,'-',1) = 'org';
	DELETE FROM  xms_bind_item
	WHERE target = old.org_id
		AND SUBSTRING_INDEX(bind_type,'-',-1) = 'org';
    END $$

DELIMITER ;

SET SQL_SAFE_UPDATES = 1;