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 datetime = dbo.[UFN_DATE_GETEARLIESTTIME](@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;
-- 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.
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;
--Update the constituent current household table...
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]
);
end
|
Trigger Definition
