TR_RELATIONSHIP_DELETE

CREATE trigger TR_RELATIONSHIP_DELETE on dbo.RELATIONSHIP after delete not for replication
as begin
    set nocount on;

    delete from
        dbo.RELATIONSHIPBYMARRIAGE
    from 
        dbo.RELATIONSHIP 
    inner join DELETED 
        on RELATIONSHIP.RELATIONSHIPCONSTITUENTID = DELETED.RECIPROCALCONSTITUENTID and
            RELATIONSHIP.RECIPROCALCONSTITUENTID = DELETED.RELATIONSHIPCONSTITUENTID and
            RELATIONSHIP.RELATIONSHIPTYPECODEID = DELETED.RECIPROCALTYPECODEID and
            RELATIONSHIP.RECIPROCALTYPECODEID = DELETED.RELATIONSHIPTYPECODEID
    where
        RELATIONSHIP.ID in (RELATIONSHIPBYMARRIAGE.RELATIONSHIPID, RELATIONSHIPBYMARRIAGE.MARRIAGERELATIONSHIPID);

    delete from
        dbo.RELATIONSHIPORGANIZATION
    from
        DELETED
    where RELATIONSHIPORGANIZATION.RELATIONSHIPSETID = DELETED.RELATIONSHIPSETID;

    delete from
        dbo.RELATIONSHIP 
    from
        DELETED
    where
        RELATIONSHIP.RELATIONSHIPCONSTITUENTID = DELETED.RECIPROCALCONSTITUENTID and
        RELATIONSHIP.RECIPROCALCONSTITUENTID = DELETED.RELATIONSHIPCONSTITUENTID and
        RELATIONSHIP.RELATIONSHIPTYPECODEID = DELETED.RECIPROCALTYPECODEID and
        RELATIONSHIP.RECIPROCALTYPECODEID = DELETED.RELATIONSHIPTYPECODEID;

    delete from
        dbo.RELATIONSHIPSET 
    from
        DELETED
    where
        RELATIONSHIPSET.ID = DELETED.RELATIONSHIPSETID;
end