create trigger TR_TEAMFUNDRAISINGTEAM_UPDATE_FAFGROUPDIRECTHIERARCHY on dbo.TEAMFUNDRAISINGTEAM after update not for replication
as
if update(PARENTTEAMID)
begin
declare @CHANGEAGENTID uniqueidentifier,
@CURRENTDATE datetime
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output
set @CURRENTDATE = GETDATE()
;With CTE_PARENT(TEAMID, PARENTTEAMID)
as
(
select D.ID, D.PARENTTEAMID
from deleted D
join dbo.TEAMEXTENSION TX on TX.TEAMFUNDRAISINGTEAMID = D.ID
union all
select C.TEAMID, TFT.PARENTTEAMID
from dbo.TEAMFUNDRAISINGTEAM TFT
join CTE_PARENT C ON TFT.ID = C.PARENTTEAMID
)
,CTE_CHILDREN(TEAMID, PARENTTEAMID)
as
(
select D.ID, D.PARENTTEAMID
from deleted D
join dbo.TEAMEXTENSION TX on TX.TEAMFUNDRAISINGTEAMID = D.ID
union all
select TFT.ID, C.PARENTTEAMID
from dbo.TEAMFUNDRAISINGTEAM TFT
join CTE_CHILDREN C ON TFT.PARENTTEAMID = C.TEAMID
),
CTE_ALL(TEAMID, PARENTTEAMID)
AS
(
select TEAMID, PARENTTEAMID from CTE_PARENT
union all
select TEAMID, PARENTTEAMID from CTE_CHILDREN
)
delete from dbo.FAFGROUPDIRECTHIERARCHY
from dbo.FAFGROUPDIRECTHIERARCHY FF
join CTE_ALL C on FF.GROUPID = C.PARENTTEAMID and FF.CHILDGROUPID = C.TEAMID
;With CTE_PARENT(TEAMID, PARENTTEAMID)
as
(
select I.ID, I.PARENTTEAMID
from inserted I
join dbo.TEAMEXTENSION TX on TX.TEAMFUNDRAISINGTEAMID = I.ID
union all
select C.TEAMID, TFT.PARENTTEAMID
from dbo.TEAMFUNDRAISINGTEAM TFT
join CTE_PARENT C ON TFT.ID = C.PARENTTEAMID
)
,CTE_CHILDREN(TEAMID, PARENTTEAMID)
as
(
select I.ID, I.PARENTTEAMID
from inserted I
join dbo.TEAMEXTENSION TX on TX.TEAMFUNDRAISINGTEAMID = I.ID
union all
select TFT.ID, C.PARENTTEAMID
from dbo.TEAMFUNDRAISINGTEAM TFT
join CTE_CHILDREN C ON TFT.PARENTTEAMID = C.TEAMID
),
CTE_ALL(TEAMID, PARENTTEAMID)
AS
(
select TEAMID, PARENTTEAMID from CTE_PARENT
union all
select TEAMID, PARENTTEAMID from CTE_CHILDREN
)
insert into dbo.FAFGROUPDIRECTHIERARCHY(ID, EVENTID, GROUPID, GROUPCONSTITUENTID, CHILDGROUPID, CHILDGROUPCONSTITUENTID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
select newID(), A.*,@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE from (
select distinct cTX.EVENTID, C.PARENTTEAMID as GROUPID , pTX.TEAMCONSTITUENTID as GROUPCONSTITUENTID, C.TEAMID as CHILDGROUPID, cTX.TEAMCONSTITUENTID as CHILDGROUPCONSTITUENTID
from CTE_ALL c
left join dbo.TEAMEXTENSION cTX (nolock) on cTX.TEAMFUNDRAISINGTEAMID = C.TEAMID
left join dbo.TEAMEXTENSION pTX (nolock) on pTX.TEAMFUNDRAISINGTEAMID = C.PARENTTEAMID
where C.PARENTTEAMID is not null
) A
end
|
Trigger Definition
