CREATE trigger TR_CONSTITUENT_UPDATE_HOUSEHOLD on dbo.CONSTITUENT after update not for replication
as begin
if update(KEYNAME) or update(KEYNAMEPREFIX) or update(FIRSTNAME) or update(MIDDLENAME) or update(MAIDENNAME) or update(NICKNAME) or update(TITLECODEID) or update(TITLE2CODEID) or update(SUFFIXCODEID) or update(SUFFIX2CODEID)
begin
if exists (select 1 from sys.TABLES where TYPE = 'U' and NAME = 'GROUPMEMBER')
and exists (select 1 from sys.TABLES where TYPE = 'U' and NAME = 'GROUPDATA')
begin
declare @CURRENTDATE datetime;
set @CURRENTDATE = GetDate();
declare @CHANGEAGENTID uniqueidentifier;
-- For audit reasons:
-- Use the same ChangeAgentId used to update the constituent row if it was updated.
-- 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;
update dbo.CONSTITUENT
set
CONSTITUENT.KEYNAME = left(dbo.UFN_NAMEFORMAT_FROMID(GROUPDATA.NAMEFORMATFUNCTIONID, PM.MEMBERID), 100),
DATECHANGED = @CURRENTDATE,
CHANGEDBYID = COALESCE(@CHANGEAGENTID, inserted.CHANGEDBYID)
from inserted
inner join dbo.GROUPMEMBER GM on inserted.ID = GM.MEMBERID
inner join dbo.GROUPDATA on GM.GROUPID = GROUPDATA.ID
inner join dbo.GROUPMEMBER PM on GM.GROUPID = PM.GROUPID and PM.ISPRIMARY <>0
where inserted.ISORGANIZATION = 0
and inserted.ISGROUP = 0
and dbo.CONSTITUENT.ID = GM.GROUPID
and GROUPDATA.NAMEFORMATFUNCTIONID is not null
end
end
end
|
Trigger Definition
