TR_EDUCATIONALHISTORYSTATUSHISTORY_MARKASCONSTITUENT

CREATE trigger TR_EDUCATIONALHISTORYSTATUSHISTORY_MARKASCONSTITUENT on dbo.EDUCATIONALHISTORYSTATUSHISTORY 
after insert, update
not for replication
as begin
  if (update(EDUCATIONALHISTORYSTATUSID) or update(EDUCATIONALHISTORYID))
  begin
    declare @ISCONSTITUENTWHENCODE tinyint;
    select @ISCONSTITUENTWHENCODE = ISCONSTITUENTWHENCODE from dbo.STUDENTCONSTITUENCYSETTINGS;

    if (@ISCONSTITUENTWHENCODE = 0)
      begin
        update dbo.CONSTITUENT set
          ISCONSTITUENT = 1,
          CHANGEDBYID = inserted.CHANGEDBYID,
          DATECHANGED = GetDate()
        from inserted
        inner join dbo.EDUCATIONALHISTORY on inserted.EDUCATIONALHISTORYID = EDUCATIONALHISTORY.ID
        where (CONSTITUENT.ID = EDUCATIONALHISTORY.CONSTITUENTID)
        and (CONSTITUENT.ISCONSTITUENT = 0)
        and (dbo.UFN_CONSTITUENT_ISSTUDENT(CONSTITUENT.ID) = 1);
      end
    else if (@ISCONSTITUENTWHENCODE = 1)
      begin
        update dbo.CONSTITUENT set
          ISCONSTITUENT = 1,
          CHANGEDBYID = inserted.CHANGEDBYID,
          DATECHANGED = GetDate()
        from inserted
        inner join dbo.EDUCATIONALHISTORY on inserted.EDUCATIONALHISTORYID = EDUCATIONALHISTORY.ID
        where (CONSTITUENT.ID = EDUCATIONALHISTORY.CONSTITUENTID)
        and (CONSTITUENT.ISCONSTITUENT = 0)
        and (dbo.UFN_CONSTITUENT_ISALUMNUS(CONSTITUENT.ID) = 1);
      end

    /* Update relationship records 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 dbo.EDUCATIONALHISTORY on RELATIONCONSTITUENCIES.RECIPROCALCONSTITUENTID = EDUCATIONALHISTORY.CONSTITUENTID 
    inner join INSERTED on EDUCATIONALHISTORY.ID = INSERTED.EDUCATIONALHISTORYID
    where RELATIONCONSTITUENCIES.CONSTITUENTID = CONSTITUENT.ID
    and CONSTITUENT.ISCONSTITUENT = 0;
  end
end