TR_RELATIONSHIP_INSERT

CREATE trigger TR_RELATIONSHIP_INSERT on dbo.RELATIONSHIP after insert not for replication
as begin

    set nocount on;    

    if dbo.UFN_B2T_CONTEXTISSYNC() = 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
                    );

        /* 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
            dbo.RELATIONSHIP
            inner join INSERTED
                on INSERTED.RELATIONSHIPCONSTITUENTID in (RELATIONSHIP.RELATIONSHIPCONSTITUENTID, RELATIONSHIP.RECIPROCALCONSTITUENTID)
                and INSERTED.ID <> RELATIONSHIP.ID
            inner join dbo.CONSTITUENT
                on CONSTITUENT.ID = INSERTED.RELATIONSHIPCONSTITUENTID
        where
            CONSTITUENT.ISORGANIZATION <> 1
            and RELATIONSHIP.ISPRIMARYBUSINESS = 1
            and INSERTED.ISPRIMARYBUSINESS = 1

        update dbo.RELATIONSHIP
        set
            RELATIONSHIP.ISPRIMARYBUSINESS = 0,
            RELATIONSHIP.DATECHANGED = INSERTED.DATECHANGED,
            RELATIONSHIP.CHANGEDBYID = INSERTED.CHANGEDBYID
        from
            dbo.RELATIONSHIP
            inner join INSERTED
                on INSERTED.RECIPROCALCONSTITUENTID in (RELATIONSHIP.RELATIONSHIPCONSTITUENTID, RELATIONSHIP.RECIPROCALCONSTITUENTID)
                and INSERTED.ID <> RELATIONSHIP.ID
            inner join dbo.CONSTITUENT
                on CONSTITUENT.ID = INSERTED.RECIPROCALCONSTITUENTID
        where
            CONSTITUENT.ISORGANIZATION <> 1
            and RELATIONSHIP.ISPRIMARYBUSINESS = 1
            and INSERTED.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
            )
    end

    /*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

    if dbo.UFN_B2T_CONTEXTISSYNC() = 0
    begin
        /*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
end