CREATE trigger [dbo].[TR_GROUPMEMBERDATERANGE_UPDATE_HOUSEHOLD_UPDATE] on [dbo].[GROUPMEMBERDATERANGE] after update not for replication
as
begin
set nocount on;
declare @CHANGEAGENTID uniqueidentifier;
declare @CURRENTDATE datetime = getdate();
declare @CURRENTDATEEARLIESTTIME date = @CURRENTDATE;
-- For audit reasons:
-- Use the same ChangeAgentId used to update the groupmemberdaterange row if it was updated.
-- If null, the set will simply coalesce out and effectively do nothing
-- Since this trigger may be hit by a bulk update, we can not assume that all ChangeByIds are the same for all records unless they aren't being specified.
if update([CHANGEDBYID])
set @CHANGEAGENTID = null;
else
exec dbo.[USP_CHANGEAGENT_GETORCREATECHANGEAGENT] @CHANGEAGENTID output;
declare @GROUPID uniqueidentifier
declare @HASPRIMARYCONTACT integer
select @GROUPID = GROUPID from dbo.[GROUPMEMBER] left join inserted on inserted.GROUPMEMBERID = [GROUPMEMBER].ID where [GROUPMEMBER].ID = inserted.GROUPMEMBERID
--check to make sure there is a non-expired primary contact.
select
@HASPRIMARYCONTACT = count([GROUPMEMBER].ID)
from dbo.[GROUPMEMBER]
inner join dbo.[GROUPMEMBERDATERANGE] on [GROUPMEMBER].ID = [GROUPMEMBERDATERANGE].GROUPMEMBERID
where
[GROUPMEMBER].GROUPID = @GROUPID
and [GROUPMEMBER].ISPRIMARY = 1
and (DATETO is null or (DATETO is not null and DATETO > GETDATE()))
-- If the groups that the updated entries belong to do not have a primary member, make the oldest entry in the group the primary
-- We will only update those groups whose membership is being altered.
if @HASPRIMARYCONTACT = 0
begin
--set isprimary = 0 for all expired members
update dbo.[GROUPMEMBER] set
[ISPRIMARY] = 0,
[DATECHANGED] = @CURRENTDATE,
[CHANGEDBYID] = isnull(@CHANGEAGENTID, [GROUPMEMBER].CHANGEDBYID)
from dbo.[GROUPMEMBER]
left join dbo.[GROUPMEMBERDATERANGE] on [GROUPMEMBER].ID = [GROUPMEMBERDATERANGE].GROUPMEMBERID
where
[GROUPMEMBER].GROUPID = @GROUPID
and [GROUPMEMBER].ISPRIMARY = 1
and DATETO is not null and DATETO < GETDATE()
update dbo.[GROUPMEMBER] set
[ISPRIMARY] = 1,
[DATECHANGED] = @CURRENTDATE,
[CHANGEDBYID] = isnull(@CHANGEAGENTID, [V1].[CHANGEDBYID])
from dbo.[GROUPMEMBER]
inner join (
select
[GM].[ID],
[GM].[ISPRIMARY],
inserted.[CHANGEDBYID],
row_number() over(partition by [GM].[GROUPID] order by [GM].[ISPRIMARY] desc, [GM].[DATEADDED] asc) as [ROWNUM]
from inserted
inner join dbo.[GROUPMEMBER] as [PM] on inserted.[GROUPMEMBERID] = [PM].[ID] and [PM].[ISPRIMARY] = 0
inner join dbo.[GROUPMEMBER] as [GM] on [PM].[GROUPID] = [GM].[GROUPID]
left join dbo.[GROUPMEMBERDATERANGE] as [GMDR] on [GM].[ID] = [GMDR].[GROUPMEMBERID]
where (([GMDR].[DATEFROM] is null and ([GMDR].[DATETO] is null or [GMDR].[DATETO] > @CURRENTDATEEARLIESTTIME))
or ([GMDR].[DATETO] is null and ([GMDR].[DATEFROM] is null or [GMDR].[DATEFROM] <= @CURRENTDATEEARLIESTTIME))
or ([GMDR].[DATEFROM] <= @CURRENTDATEEARLIESTTIME and [GMDR].[DATETO] > @CURRENTDATEEARLIESTTIME))
) as [V1] on [GROUPMEMBER].[ID] = [V1].[ID] and [V1].[ROWNUM] = 1 and [V1].[ISPRIMARY] = 0;
end
--Update the constituent current household table...
-- This should run each time in case a member exists in the CONSTITUENTHOUSEHOLD table and is added back.
update dbo.[CONSTITUENTHOUSEHOLD] set
[HOUSEHOLDID] = [GROUPMEMBER].[GROUPID]
from dbo.[GROUPMEMBER]
inner join dbo.[GROUPDATA] on [GROUPDATA].[ID] = [GROUPMEMBER].[GROUPID]
inner join dbo.[CONSTITUENTHOUSEHOLD] on [CONSTITUENTHOUSEHOLD].[ID] = [GROUPMEMBER].[MEMBERID]
left join dbo.[GROUPMEMBERDATERANGE] as [GMDR] on [GMDR].[GROUPMEMBERID] = [GROUPMEMBER].[ID]
where
(([GMDR].[DATEFROM] is null and ([GMDR].[DATETO] is null or [GMDR].[DATETO] > @CURRENTDATEEARLIESTTIME))
or ([GMDR].[DATETO] is null and ([GMDR].[DATEFROM] is null or [GMDR].[DATEFROM] <= @CURRENTDATEEARLIESTTIME))
or ([GMDR].[DATEFROM] <= @CURRENTDATEEARLIESTTIME and [GMDR].[DATETO] > @CURRENTDATEEARLIESTTIME))
and [GROUPDATA].[GROUPTYPECODE] = 0
and exists(
select [ID]
from inserted
where
[GROUPMEMBER].[ID] = inserted.[GROUPMEMBERID]
);
if update([DATETO])
update dbo.[CONSTITUENTHOUSEHOLD] set
[HOUSEHOLDID] = null,
[ISPRIMARYMEMBER] = 0
from dbo.[GROUPMEMBER]
inner join dbo.[GROUPDATA] on [GROUPDATA].[ID] = [GROUPMEMBER].[GROUPID]
inner join dbo.[CONSTITUENTHOUSEHOLD] on [CONSTITUENTHOUSEHOLD].[ID] = [GROUPMEMBER].[MEMBERID]
inner join inserted on inserted.[GROUPMEMBERID] = [GROUPMEMBER].[ID]
where
inserted.[DATETO] is not null
and [GROUPDATA].[GROUPTYPECODE] = 0;
end
|
Trigger Definition
