CREATE trigger TR_RELATIONSHIP_UPDATE ON dbo.RELATIONSHIP after update not for replication
as begin
set nocount on;
/*Update reciprocal record's type information if relationship dates have changed*/
if update(STARTDATE) or update(ENDDATE) begin
update
dbo.RELATIONSHIP
set
RELATIONSHIP.STARTDATE = INSERTED.STARTDATE,
RELATIONSHIP.ENDDATE = INSERTED.ENDDATE,
RELATIONSHIP.DATECHANGED = INSERTED.DATECHANGED,
RELATIONSHIP.CHANGEDBYID = INSERTED.CHANGEDBYID
from
INSERTED
inner join
DELETED
on
DELETED.ID = INSERTED.ID
where
RELATIONSHIP.RELATIONSHIPCONSTITUENTID = DELETED.RECIPROCALCONSTITUENTID and
RELATIONSHIP.RECIPROCALCONSTITUENTID = DELETED.RELATIONSHIPCONSTITUENTID and
RELATIONSHIP.RELATIONSHIPTYPECODEID = DELETED.RECIPROCALTYPECODEID and
RELATIONSHIP.RECIPROCALTYPECODEID = DELETED.RELATIONSHIPTYPECODEID
end
/*Update comments field if it has changed*/
if update(COMMENTS) begin
update
dbo.RELATIONSHIP
set
RELATIONSHIP.COMMENTS = INSERTED.COMMENTS,
RELATIONSHIP.DATECHANGED = INSERTED.DATECHANGED,
RELATIONSHIP.CHANGEDBYID = INSERTED.CHANGEDBYID
from
INSERTED
inner join
DELETED
on
DELETED.ID = INSERTED.ID
where
RELATIONSHIP.RELATIONSHIPCONSTITUENTID = DELETED.RECIPROCALCONSTITUENTID and
RELATIONSHIP.RECIPROCALCONSTITUENTID = DELETED.RELATIONSHIPCONSTITUENTID and
RELATIONSHIP.RELATIONSHIPTYPECODEID = DELETED.RECIPROCALTYPECODEID and
RELATIONSHIP.RECIPROCALTYPECODEID = DELETED.RELATIONSHIPTYPECODEID
end
/*Update reciprocal record's contact information if relationship type has changed*/
if update(ISCONTACT) or update(CONTACTTYPECODEID) or update(POSITION) begin
update
dbo.RELATIONSHIP
set
RELATIONSHIP.ISCONTACT = INSERTED.ISCONTACT,
RELATIONSHIP.CONTACTTYPECODEID = INSERTED.CONTACTTYPECODEID,
RELATIONSHIP.POSITION = INSERTED.POSITION,
RELATIONSHIP.DATECHANGED = INSERTED.DATECHANGED,
RELATIONSHIP.CHANGEDBYID = INSERTED.CHANGEDBYID
from
INSERTED
inner join
DELETED
on
DELETED.ID = INSERTED.ID
where
RELATIONSHIP.RELATIONSHIPCONSTITUENTID = DELETED.RECIPROCALCONSTITUENTID and
RELATIONSHIP.RECIPROCALCONSTITUENTID = DELETED.RELATIONSHIPCONSTITUENTID and
RELATIONSHIP.RELATIONSHIPTYPECODEID = DELETED.RECIPROCALTYPECODEID and
RELATIONSHIP.RECIPROCALTYPECODEID = DELETED.RELATIONSHIPTYPECODEID
end
/*Update primary business records if IsPrimaryBusiness field has changed*/
if update(ISPRIMARYBUSINESS) begin
if (select count(ID) from inserted where ISPRIMARYBUSINESS = 1) > 0 begin
/*Remove any references to any other business 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
)
end
/*Update reciprocal record's primary business information*/
update
dbo.RELATIONSHIP
set
RELATIONSHIP.ISPRIMARYBUSINESS = INSERTED.ISPRIMARYBUSINESS,
RELATIONSHIP.DATECHANGED = INSERTED.DATECHANGED,
RELATIONSHIP.CHANGEDBYID = INSERTED.CHANGEDBYID
from
INSERTED
inner join
DELETED
on
DELETED.ID = INSERTED.ID
where
RELATIONSHIP.RELATIONSHIPCONSTITUENTID = DELETED.RECIPROCALCONSTITUENTID and
RELATIONSHIP.RECIPROCALCONSTITUENTID = DELETED.RELATIONSHIPCONSTITUENTID and
RELATIONSHIP.RELATIONSHIPTYPECODEID = DELETED.RECIPROCALTYPECODEID and
RELATIONSHIP.RECIPROCALTYPECODEID = DELETED.RELATIONSHIPTYPECODEID and
RELATIONSHIP.ISPRIMARYBUSINESS = DELETED.ISPRIMARYBUSINESS
end
/*Update primary contact records if IsPrimaryContact field has changed*/
if update(ISPRIMARYCONTACT) begin
if (select count(ID) from inserted where ISPRIMARYCONTACT = 1) > 0 begin
/*Remove any references to any other individual 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
)
end
/*Update reciprocal record's primary contact information*/
update
dbo.RELATIONSHIP
set
RELATIONSHIP.ISPRIMARYCONTACT = INSERTED.ISPRIMARYCONTACT,
RELATIONSHIP.DATECHANGED = INSERTED.DATECHANGED,
RELATIONSHIP.CHANGEDBYID = INSERTED.CHANGEDBYID
from
INSERTED
inner join
DELETED
on
DELETED.ID = INSERTED.ID
where
RELATIONSHIP.RELATIONSHIPCONSTITUENTID = DELETED.RECIPROCALCONSTITUENTID and
RELATIONSHIP.RECIPROCALCONSTITUENTID = DELETED.RELATIONSHIPCONSTITUENTID and
RELATIONSHIP.RELATIONSHIPTYPECODEID = DELETED.RECIPROCALTYPECODEID and
RELATIONSHIP.RECIPROCALTYPECODEID = DELETED.RELATIONSHIPTYPECODEID and
RELATIONSHIP.ISPRIMARYCONTACT = DELETED.ISPRIMARYCONTACT
end
/*Update reciprocal record's type information if relationship type has changed*/
if update(RELATIONSHIPTYPECODEID) or update(RECIPROCALTYPECODEID) or update(ISMATCHINGGIFTRELATIONSHIP) begin
update
dbo.RELATIONSHIP
set
RELATIONSHIP.RELATIONSHIPTYPECODEID = INSERTED.RECIPROCALTYPECODEID,
RELATIONSHIP.RECIPROCALTYPECODEID = INSERTED.RELATIONSHIPTYPECODEID,
RELATIONSHIP.ISMATCHINGGIFTRELATIONSHIP = INSERTED.ISMATCHINGGIFTRELATIONSHIP,
RELATIONSHIP.DATECHANGED = INSERTED.DATECHANGED,
RELATIONSHIP.CHANGEDBYID = INSERTED.CHANGEDBYID
from
INSERTED
inner join
DELETED
on
DELETED.ID = INSERTED.ID
where
RELATIONSHIP.RELATIONSHIPCONSTITUENTID = DELETED.RECIPROCALCONSTITUENTID and
RELATIONSHIP.RECIPROCALCONSTITUENTID = DELETED.RELATIONSHIPCONSTITUENTID and
RELATIONSHIP.RELATIONSHIPTYPECODEID = DELETED.RECIPROCALTYPECODEID and
RELATIONSHIP.RECIPROCALTYPECODEID = DELETED.RELATIONSHIPTYPECODEID and
RELATIONSHIP.ISMATCHINGGIFTRELATIONSHIP = DELETED.ISMATCHINGGIFTRELATIONSHIP
end
/*Update ISCONSTITUENT based on Student Relationship Constituency Criteria*/
if update(RELATIONSHIPTYPECODEID)
begin
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
end
if update(RECIPROCALTYPECODEID)
begin
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
/*Update spouse records if IsSpouse field has changed*/
if update(ISSPOUSE) begin
if (select count(ID) from inserted where ISSPOUSE = 1) > 0 begin
/*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
)
/*Set the IsSpouse flag on the reciprocal relationship to true*/
update
dbo.RELATIONSHIP
set
RELATIONSHIP.ISSPOUSE = 1,
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.RECIPROCALCONSTITUENTID
where
RELATIONSHIP.RECIPROCALCONSTITUENTID = INSERTED.RELATIONSHIPCONSTITUENTID and
RELATIONSHIP.RELATIONSHIPTYPECODEID = INSERTED.RECIPROCALTYPECODEID and
RELATIONSHIP.RECIPROCALTYPECODEID = INSERTED.RELATIONSHIPTYPECODEID and
RELATIONSHIP.ID <> INSERTED.ID
)
/* 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
end
if (select count(ID) from inserted where ISSPOUSE = 0) > 0 begin
/*Set the IsSpouse flag on the reciprocal relationship to false*/
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.RECIPROCALCONSTITUENTID
where
RELATIONSHIP.RECIPROCALCONSTITUENTID = INSERTED.RELATIONSHIPCONSTITUENTID and
RELATIONSHIP.RELATIONSHIPTYPECODEID = INSERTED.RECIPROCALTYPECODEID and
RELATIONSHIP.RECIPROCALTYPECODEID = INSERTED.RELATIONSHIPTYPECODEID and
RELATIONSHIP.ID <> INSERTED.ID and
RELATIONSHIP.ISSPOUSE = 1
)
end
end
if update(RELATIONSHIPCONSTITUENTID) or update(RECIPROCALCONSTITUENTID) begin
update dbo.RELATIONSHIP
set RELATIONSHIP.RELATIONSHIPCONSTITUENTID = INSERTED.RECIPROCALCONSTITUENTID,
RELATIONSHIP.RECIPROCALCONSTITUENTID = INSERTED.RELATIONSHIPCONSTITUENTID,
RELATIONSHIP.DATECHANGED = INSERTED.DATECHANGED,
RELATIONSHIP.CHANGEDBYID = INSERTED.CHANGEDBYID
from INSERTED
inner join DELETED on DELETED.ID = INSERTED.ID
where
RELATIONSHIP.RELATIONSHIPCONSTITUENTID = DELETED.RECIPROCALCONSTITUENTID and
RELATIONSHIP.RECIPROCALCONSTITUENTID = DELETED.RELATIONSHIPCONSTITUENTID and
RELATIONSHIP.RELATIONSHIPTYPECODEID = DELETED.RECIPROCALTYPECODEID and
RELATIONSHIP.RECIPROCALTYPECODEID = DELETED.RELATIONSHIPTYPECODEID
end
end
|
Trigger Definition
