CREATE trigger [dbo].[TR_GROUPMEMBER_DELETE_HOUSEHOLD_UPDATE] on [dbo].[GROUPMEMBER] after delete not for replication
as
begin
set nocount on;
declare @CHANGEAGENTID uniqueidentifier;
declare @CURRENTDATE datetime = getdate();
declare @CURRENTDATEEARLIESTTIME date = @CURRENTDATE;
/*make sure you get the information from context cache if available since this is set during delete */
exec dbo.[USP_CHANGEAGENT_GETORCREATECHANGEAGENTFROMCONTEXT] @CHANGEAGENTID output;
-- If the groups that the deleted 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] = @CHANGEAGENTID
from dbo.[GROUPMEMBER]
inner join (
select
[GM].[ID],
row_number() over(partition by [GM].[GROUPID] order by [GM].[DATEADDED]) as [ROWNUM]
from deleted
inner join dbo.[GROUPMEMBER] as [GM] on deleted.[GROUPID] = [GM].[GROUPID]
left join dbo.[GROUPMEMBERDATERANGE] as [GMDR] on [GM].[ID] = [GMDR].[GROUPMEMBERID]
where deleted.[ISPRIMARY] <> 0
and (([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;
update dbo.[CONSTITUENT] set
[KEYNAME] = left(dbo.[UFN_NAMEFORMAT_FROMID]([GROUPDATA].[NAMEFORMATFUNCTIONID], [PM].[MEMBERID]), 100),
[DATECHANGED] = @CURRENTDATE,
[CHANGEDBYID] = @CHANGEAGENTID
from deleted
inner join dbo.[GROUPDATA] on deleted.[GROUPID] = [GROUPDATA].[ID]
inner join dbo.[GROUPMEMBER] as [PM] on deleted.[GROUPID] = [PM].[GROUPID] and [PM].[ISPRIMARY] <> 0
where [CONSTITUENT].[ID] = deleted.[GROUPID]
and [GROUPDATA].[NAMEFORMATFUNCTIONID] is not null
and deleted.ISPRIMARY = 0; -- update of group keyname upon delete of primary member is handled by TR_GROUPMEMBER_INSERT_HOUSEHOLD_UPDATE, fired after update isprimary=1 above
--Update the constituent current household table...
update dbo.[CONSTITUENTHOUSEHOLD] set
[HOUSEHOLDID] = null,
[ISPRIMARYMEMBER] = 0
from deleted
inner join dbo.[GROUPDATA] on [GROUPDATA].[ID] = deleted.[GROUPID]
inner join dbo.[CONSTITUENTHOUSEHOLD] on [CONSTITUENTHOUSEHOLD].[ID] = deleted.[MEMBERID] and [CONSTITUENTHOUSEHOLD].[HOUSEHOLDID] = deleted.[GROUPID]
where [GROUPDATA].[GROUPTYPECODE] = 0;
end
|
Trigger Definition
