CREATE trigger TR_ORGANIZATIONDATA_INSERTUPDATE on dbo.ORGANIZATIONDATA for update, insert
as begin
set nocount on
-- 9/11/12 CEV - constituent relation merge, do not change history
-- Previously had disabled trigger in merge but this can cause a schema lock (WI 231248)
begin try
declare @MERGEWELLKNOWNGUID uniqueidentifier = '3DB5F72B-2BA0-45A0-890F-24359E3F42A8';
if cast(CONTEXT_INFO() as uniqueidentifier) = @MERGEWELLKNOWNGUID
return;
end try
begin catch
end catch
if update(PARENTCORPID)
begin
declare @CURRENTDATE datetime = getdate()
declare @CHANGEAGENTID uniqueidentifier
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output
declare @CONTEXTCACHE varbinary(128)
set @CONTEXTCACHE = CONTEXT_INFO()
if @CHANGEAGENTID is not null
set CONTEXT_INFO @CHANGEAGENTID
delete OPH
from dbo.ORGANIZATIONPARENTHISTORY OPH
inner join inserted i on OPH.CHILDCORPID = i.ID
left join deleted d on i.PARENTCORPID = d.PARENTCORPID
where OPH.STARTDATE = cast(@CURRENTDATE as date)
and d.PARENTCORPID is null
if not @CONTEXTCACHE is null
set CONTEXT_INFO @CONTEXTCACHE
update OPH
set OPH.ENDDATE = DateAdd("d", -1, @CURRENTDATE),
OPH.DATECHANGED = @CURRENTDATE,
OPH.CHANGEDBYID = @CHANGEAGENTID
from dbo.ORGANIZATIONPARENTHISTORY OPH
inner join deleted d on d.ID = OPH.CHILDCORPID and d.PARENTCORPID = OPH.PARENTCORPID
left join inserted i on d.PARENTCORPID = i.PARENTCORPID
where i.PARENTCORPID is null
and (not d.PARENTCORPID is null)
and OPH.ENDDATE is null
insert into dbo.ORGANIZATIONPARENTHISTORY(ID, CHILDCORPID, PARENTCORPID, STARTDATE, DATEADDED, DATECHANGED, ADDEDBYID, CHANGEDBYID)
(select newid(),
i.ID,
i.PARENTCORPID,
cast(@CURRENTDATE as date),
@CURRENTDATE,
@CURRENTDATE,
@CHANGEAGENTID,
@CHANGEAGENTID
from inserted i
left join deleted d on i.ID = d.id
where (d.PARENTCORPID <> i.PARENTCORPID
or d.PARENTCORPID is null)
and (not i.PARENTCORPID is null))
end
end
|
Trigger Definition
