CREATE trigger TR_RELATIONSHIP_INSERT on dbo.RELATIONSHIP after insert not for replication
as begin
set nocount on;
/*Remove any references to the current record as a spouse*/
update
dbo.RELATIONSHIP
set
RELATIONSHIP.ISSPOUSE = 0,
RELATIONSHIP.DATECHANGED = INSERTED.DATECHANGED,
RELATIONSHIP.CHANGEDBYID = INSERTED.CHANGEDBYID
from
INSERTED
where RELATIONSHIP.ID in(
select
RELATIONSHIP.ID
from
dbo.RELATIONSHIP
inner join
INSERTED
on
RELATIONSHIP.RELATIONSHIPCONSTITUENTID = INSERTED.RELATIONSHIPCONSTITUENTID
where
RELATIONSHIP.ID <> INSERTED.ID and
INSERTED.ISSPOUSE = 1 and
RELATIONSHIP.ISSPOUSE = 1
union all
select
RELATIONSHIP.ID
from
dbo.RELATIONSHIP
inner join
INSERTED
on
RELATIONSHIP.RELATIONSHIPCONSTITUENTID = INSERTED.RECIPROCALCONSTITUENTID
where
RELATIONSHIP.ID <> INSERTED.ID and
INSERTED.ISSPOUSE = 1 and
RELATIONSHIP.ISSPOUSE = 1
union all
select
RELATIONSHIP.ID
from
dbo.RELATIONSHIP
inner join
INSERTED
on
RELATIONSHIP.RECIPROCALCONSTITUENTID = INSERTED.RELATIONSHIPCONSTITUENTID
where
RELATIONSHIP.ID <> INSERTED.ID and
INSERTED.ISSPOUSE = 1 and
RELATIONSHIP.ISSPOUSE = 1
union all
select
RELATIONSHIP.ID
from
dbo.RELATIONSHIP
inner join
INSERTED
on
RELATIONSHIP.RECIPROCALCONSTITUENTID = INSERTED.RECIPROCALCONSTITUENTID
where
RELATIONSHIP.ID <> INSERTED.ID and
INSERTED.ISSPOUSE = 1 and
RELATIONSHIP.ISSPOUSE = 1
);
/* If constituent spouses are supposed to be marked as spouses make sure the new spouse is marked as a constituent */
if (dbo.UFN_CONSTITUENCYCRITERIASPOUSE_SPOUSEISCONSTITUENT() = 1)
begin
update dbo.CONSTITUENT
set CONSTITUENT.ISCONSTITUENT = 1,
CONSTITUENT.DATECHANGED = INSERTED.DATECHANGED,
CONSTITUENT.CHANGEDBYID = INSERTED.CHANGEDBYID
from dbo.CONSTITUENT
inner join INSERTED
on INSERTED.RECIPROCALCONSTITUENTID = CONSTITUENT.ID
inner join dbo.CONSTITUENT as RELATIONSHIPCONSTITUENT
on INSERTED.RELATIONSHIPCONSTITUENTID = RELATIONSHIPCONSTITUENT.ID
where (INSERTED.ISSPOUSE = 1) and
(CONSTITUENT.ISCONSTITUENT = 0) and
(RELATIONSHIPCONSTITUENT.ISCONSTITUENT = 1);
end
/*Remove any references to the current record as a primary business*/
update
dbo.RELATIONSHIP
set
RELATIONSHIP.ISPRIMARYBUSINESS = 0,
RELATIONSHIP.DATECHANGED = INSERTED.DATECHANGED,
RELATIONSHIP.CHANGEDBYID = INSERTED.CHANGEDBYID
from
INSERTED
inner join
dbo.CONSTITUENT
on
INSERTED.RELATIONSHIPCONSTITUENTID = CONSTITUENT.ID
where
CONSTITUENT.ISORGANIZATION = 0 and
RELATIONSHIP.ID in(
select
RELATIONSHIP.ID
from
dbo.RELATIONSHIP
inner join
INSERTED
on
RELATIONSHIP.RELATIONSHIPCONSTITUENTID = INSERTED.RELATIONSHIPCONSTITUENTID
where
RELATIONSHIP.ID <> INSERTED.ID and
INSERTED.ISPRIMARYBUSINESS = 1 and
RELATIONSHIP.ISPRIMARYBUSINESS = 1
union all
select
RELATIONSHIP.ID
from
dbo.RELATIONSHIP
inner join
INSERTED
on
RELATIONSHIP.RECIPROCALCONSTITUENTID = INSERTED.RELATIONSHIPCONSTITUENTID
where
RELATIONSHIP.RELATIONSHIPCONSTITUENTID <> INSERTED.RECIPROCALCONSTITUENTID and
INSERTED.ISPRIMARYBUSINESS = 1 and
RELATIONSHIP.ISPRIMARYBUSINESS = 1
)
update
dbo.RELATIONSHIP
set
RELATIONSHIP.ISPRIMARYBUSINESS = 0,
RELATIONSHIP.DATECHANGED = INSERTED.DATECHANGED,
RELATIONSHIP.CHANGEDBYID = INSERTED.CHANGEDBYID
from
INSERTED
inner join
dbo.CONSTITUENT
on
INSERTED.RECIPROCALCONSTITUENTID = CONSTITUENT.ID
where
CONSTITUENT.ISORGANIZATION = 0 and
RELATIONSHIP.ID in
(
select
RELATIONSHIP.ID
from
dbo.RELATIONSHIP
inner join
INSERTED
on
RELATIONSHIP.RELATIONSHIPCONSTITUENTID = INSERTED.RECIPROCALCONSTITUENTID
where
INSERTED.ISPRIMARYBUSINESS = 1 and
RELATIONSHIP.ISPRIMARYBUSINESS = 1 and
RELATIONSHIP.RECIPROCALCONSTITUENTID <> INSERTED.RELATIONSHIPCONSTITUENTID
union all
select
RELATIONSHIP.ID
from
dbo.RELATIONSHIP
inner join
INSERTED
on
RELATIONSHIP.RECIPROCALCONSTITUENTID = INSERTED.RECIPROCALCONSTITUENTID
where
RELATIONSHIP.ID <> INSERTED.ID and
INSERTED.ISPRIMARYBUSINESS = 1 and
RELATIONSHIP.ISPRIMARYBUSINESS = 1
)
/*Remove any references to the current record as a primary contact*/
update
dbo.RELATIONSHIP
set
RELATIONSHIP.ISPRIMARYCONTACT = 0,
RELATIONSHIP.DATECHANGED = INSERTED.DATECHANGED,
RELATIONSHIP.CHANGEDBYID = INSERTED.CHANGEDBYID
from
INSERTED
inner join
dbo.CONSTITUENT
on
INSERTED.RELATIONSHIPCONSTITUENTID = CONSTITUENT.ID
where
CONSTITUENT.ISORGANIZATION = 1 and
RELATIONSHIP.ID in(
select
RELATIONSHIP.ID
from
dbo.RELATIONSHIP
inner join
INSERTED
on
RELATIONSHIP.RELATIONSHIPCONSTITUENTID = INSERTED.RELATIONSHIPCONSTITUENTID
where
RELATIONSHIP.ID <> INSERTED.ID and
INSERTED.ISPRIMARYCONTACT = 1 and
RELATIONSHIP.ISPRIMARYCONTACT = 1
union all
select
RELATIONSHIP.ID
from
dbo.RELATIONSHIP
inner join
INSERTED
on
RELATIONSHIP.RECIPROCALCONSTITUENTID = INSERTED.RELATIONSHIPCONSTITUENTID
where
RELATIONSHIP.RELATIONSHIPCONSTITUENTID <> INSERTED.RECIPROCALCONSTITUENTID and
INSERTED.ISPRIMARYCONTACT = 1 and
RELATIONSHIP.ISPRIMARYCONTACT = 1
)
update
dbo.RELATIONSHIP
set
RELATIONSHIP.ISPRIMARYCONTACT = 0,
RELATIONSHIP.DATECHANGED = INSERTED.DATECHANGED,
RELATIONSHIP.CHANGEDBYID = INSERTED.CHANGEDBYID
from
INSERTED
inner join
dbo.CONSTITUENT
on
INSERTED.RECIPROCALCONSTITUENTID = CONSTITUENT.ID
where
CONSTITUENT.ISORGANIZATION = 1 and
RELATIONSHIP.ID in
(
select
RELATIONSHIP.ID
from
dbo.RELATIONSHIP
inner join
INSERTED
on
RELATIONSHIP.RELATIONSHIPCONSTITUENTID = INSERTED.RECIPROCALCONSTITUENTID
where
INSERTED.ISPRIMARYCONTACT = 1 and
RELATIONSHIP.ISPRIMARYCONTACT = 1 and
RELATIONSHIP.RECIPROCALCONSTITUENTID <> INSERTED.RELATIONSHIPCONSTITUENTID
union all
select
RELATIONSHIP.ID
from
dbo.RELATIONSHIP
inner join
INSERTED
on
RELATIONSHIP.RECIPROCALCONSTITUENTID = INSERTED.RECIPROCALCONSTITUENTID
where
RELATIONSHIP.ID <> INSERTED.ID and
INSERTED.ISPRIMARYCONTACT = 1 and
RELATIONSHIP.ISPRIMARYCONTACT = 1
)
/*Create the reciprocal relationship record*/
insert into dbo.RELATIONSHIP
(
RELATIONSHIPCONSTITUENTID,
RECIPROCALCONSTITUENTID,
RELATIONSHIPTYPECODEID,
RECIPROCALTYPECODEID,
ISSPOUSE,
ISPRIMARYBUSINESS,
ISCONTACT,
ISPRIMARYCONTACT,
CONTACTTYPECODEID,
POSITION,
STARTDATE,
ENDDATE,
ISMATCHINGGIFTRELATIONSHIP,
RELATIONSHIPSETID,
COMMENTS,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
RECIPROCALCONSTITUENTID,
RELATIONSHIPCONSTITUENTID,
RECIPROCALTYPECODEID,
RELATIONSHIPTYPECODEID,
ISSPOUSE,
ISPRIMARYBUSINESS,
ISCONTACT,
ISPRIMARYCONTACT,
CONTACTTYPECODEID,
POSITION,
STARTDATE,
ENDDATE,
ISMATCHINGGIFTRELATIONSHIP,
RELATIONSHIPSETID,
COMMENTS,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
from
INSERTED
/*Update ISCONSTITUENT on the RELATIONSHIPCONSTITUENTID record based on Student Relationship Constituency Criteria*/
update dbo.CONSTITUENT
set ISCONSTITUENT = 1,
CHANGEDBYID = INSERTED.CHANGEDBYID,
DATECHANGED = GetDate()
from
dbo.UFN_CONSTITUENT_STUDENTRELATIONCONSTITUENCIES() as RELATIONCONSTITUENCIES
inner join
INSERTED on
RELATIONCONSTITUENCIES.RECIPROCALCONSTITUENTID = INSERTED.RECIPROCALCONSTITUENTID and
RELATIONCONSTITUENCIES.CONSTITUENTID = INSERTED.RELATIONSHIPCONSTITUENTID and
RELATIONCONSTITUENCIES.RECIPROCALTYPECODEID = INSERTED.RECIPROCALTYPECODEID and
RELATIONCONSTITUENCIES.RELATIONSHIPTYPECODEID = INSERTED.RELATIONSHIPTYPECODEID
where
RELATIONCONSTITUENCIES.CONSTITUENTID = CONSTITUENT.ID
and CONSTITUENT.ISCONSTITUENT = 0
/*Update ISCONSTITUENT on the RECIPROCALCONSTITUENTID record based on Student Relationship Constituency Criteria*/
update dbo.CONSTITUENT
set ISCONSTITUENT = 1,
CHANGEDBYID = INSERTED.CHANGEDBYID,
DATECHANGED = GetDate()
from
dbo.UFN_CONSTITUENT_STUDENTRELATIONCONSTITUENCIES() as RELATIONCONSTITUENCIES
inner join
INSERTED on
RELATIONCONSTITUENCIES.RECIPROCALCONSTITUENTID = INSERTED.RELATIONSHIPCONSTITUENTID and
RELATIONCONSTITUENCIES.CONSTITUENTID = INSERTED.RECIPROCALCONSTITUENTID and
RELATIONCONSTITUENCIES.RECIPROCALTYPECODEID = INSERTED.RELATIONSHIPTYPECODEID and
RELATIONCONSTITUENCIES.RELATIONSHIPTYPECODEID = INSERTED.RECIPROCALTYPECODEID
where
RELATIONCONSTITUENCIES.CONSTITUENTID = CONSTITUENT.ID
and CONSTITUENT.ISCONSTITUENT = 0
end
|
Trigger Definition
