人事表(staff_id staff_name)
表crm_chance,crm_service,crm_payout,crm_pact,crm_lr都有字段STAFF_ID,
当删除人事表中的一条记录时,如何判断其他表中是否存在,如果存在,不能删除,反之可以.
表crm_chance,crm_service,crm_payout,crm_pact,crm_lr都有字段STAFF_ID,
当删除人事表中的一条记录时,如何判断其他表中是否存在,如果存在,不能删除,反之可以.
WHERE NOT EXISTS(SELECT 1 FROM crm_chance WHERE STAFF_ID=A.STAFF_ID)
AND NOT EXISTS(SELECT 1 FROM crm_service WHERE STAFF_ID=A.STAFF_ID)
AND NOT EXISTS(SELECT 1 FROM crm_pact WHERE STAFF_ID=A.STAFF_ID)
AND NOT EXISTS(SELECT 1 FROM crm_lr WHERE STAFF_ID=A.STAFF_ID)
表crm_chance,crm_service,crm_payout,crm_pact,crm_lr都有字段STAFF_ID,
这些表的STAFF_ID字段
都要参照人事表吧,人事表是主表吧 人事表(staff_id staff_name) 在表crm_chance,crm_service,crm_payout,crm_pact,crm_lr 上建外键引用人事表的staff_id就行
DELETE A FROM 人事表 A
WHERE NOT EXISTS(SELECT 1 FROM crm_chance WHERE STAFF_ID=A.STAFF_ID)
AND NOT EXISTS(SELECT 1 FROM crm_service WHERE STAFF_ID=A.STAFF_ID)
AND NOT EXISTS(SELECT 1 FROM crm_pact WHERE STAFF_ID=A.STAFF_ID)
AND NOT EXISTS(SELECT 1 FROM crm_lr WHERE STAFF_ID=A.STAFF_ID)
WHERE NOT EXISTS(SELECT 1 FROM crm_chance WHERE STAFF_ID=A.STAFF_ID)
AND NOT EXISTS(SELECT 1 FROM crm_service WHERE STAFF_ID=A.STAFF_ID)
AND NOT EXISTS(SELECT 1 FROM crm_pact WHERE STAFF_ID=A.STAFF_ID)
AND NOT EXISTS(SELECT 1 FROM crm_lr WHERE STAFF_ID=A.STAFF_ID)
是相当的慢.有没有更方便面的方法,同时,会在前台提示用户"不能删除"