TR_RELATIONSHIP_DELETE (Trigger)

                 
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.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