IF OBJECT_ID ('[NB_Personal_ChannelSetChannel]', 'TR') IS NOT NULL
   DROP TRIGGER NB_Personal_ChannelSetChannel;
 
GO
 
CREATE TRIGGER [NB_Personal_ChannelSetChannel]  
ON [NB_Personal]  
AFTER INSERT, DELETE   
AS 
BEGIN
    INSERT INTO NB_ChannelSetChannel 
    SELECT 
        inserted.ChannelID, 
        PrincipalID, 
        ChannelSetID, 
        3 as TYPE,
        NB_Channel.PermissionChannel
    FROM inserted inner join NB_Channel on NB_Channel.ChannelID = inserted.ChannelID
 
    DELETE NB_ChannelSetChannel FROM NB_ChannelSetChannel 
    INNER JOIN deleted ON 
        deleted.ChannelID = NB_ChannelSetChannel.ChannelID AND 
        deleted.ChannelSetID = NB_ChannelSetChannel.ChannelSetID AND 
        deleted.PrincipalID = NB_ChannelSetChannel.PrincipalID
    WHERE Type = 3
END
 
GO
 
DELETE FROM NB_ChannelSetChannel 
INSERT INTO NB_ChannelSetChannel SELECT NB_Channel.ChannelID,PrincipalID,ChannelSetID,1 as TYPE, PermissionChannel FROM NB_Mandatory INNER JOIN NB_Channel ON NB_Mandatory.ChannelID = NB_Channel.ChannelID
INSERT INTO NB_ChannelSetChannel SELECT NB_Channel.ChannelID,PrincipalID,ChannelSetID,2 as TYPE, PermissionChannel FROM NB_Suggested INNER JOIN NB_Channel ON NB_Suggested.ChannelID = NB_Channel.ChannelID GROUP BY NB_Channel.ChannelID,PrincipalID,ChannelSetID,PermissionChannel
INSERT INTO NB_ChannelSetChannel SELECT NB_Channel.ChannelID,PrincipalID,ChannelSetID,3 as TYPE, PermissionChannel FROM NB_Personal INNER JOIN NB_Channel ON NB_Personal.ChannelID = NB_Channel.ChannelID
 
GO