CREATE TRIGGER tg_principal ON SECURITY_PRINCIPAL FOR DELETE
AS
DECLARE @PrincipalID INT
SELECT @PrincipalID = (SELECT PRINCIPAL_ID FROM Deleted)
DELETE FROM SSO_PRINCIPAL_TO_REMOTE WHERE PRINCIPAL_ID = @PrincipalID OR REMOTE_PRINCIPAL_ID = @PrincipalID
DELETE FROM SECURITY_USER_ROLE WHERE USER_ID = @PrincipalID OR ROLE_ID = @PrincipalID
DELETE FROM SECURITY_USER_GROUP WHERE USER_ID = @PrincipalID OR GROUP_ID = @PrincipalID
DELETE FROM SECURITY_GROUP_ROLE WHERE ROLE_ID = @PrincipalID OR GROUP_ID = @PrincipalID
;