CREATE trigger TR_TEAMEXTENSION_INSERT_FAFGROUPDIRECTHIERARCHY on dbo.TEAMEXTENSION after insert not for replication
as begin
declare @CHANGEAGENTID uniqueidentifier,
@CURRENTDATE datetime
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output
set @CURRENTDATE = GETDATE()
;With CTE_PARENT(TEAMID, PARENTTEAMID)
AS
(
select T.ID, T.PARENTTEAMID
from inserted I
join dbo.TEAMFUNDRAISINGTEAM T on I.TEAMFUNDRAISINGTEAMID = T.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 T.ID, T.PARENTTEAMID
from inserted I
join dbo.TEAMFUNDRAISINGTEAM T on I.TEAMFUNDRAISINGTEAMID = T.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
-- insert self record.
insert into dbo.FAFGROUPDIRECTHIERARCHY(ID, EVENTID, GROUPID, GROUPCONSTITUENTID, CHILDGROUPID, CHILDGROUPCONSTITUENTID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
select newID(), I.EVENTID, I.TEAMFUNDRAISINGTEAMID, I.TEAMCONSTITUENTID, I.TEAMFUNDRAISINGTEAMID, I.TEAMCONSTITUENTID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
from inserted I
end
|
Trigger Definition
