TR_EDUCATIONALHISTORY_MARKASCONSTITUENT (Trigger)

        
CREATE trigger TR_EDUCATIONALHISTORY_MARKASCONSTITUENT 
  on dbo.EDUCATIONALHISTORY 
  after insert, update
  not for replication
as begin
  if update(CONSTITUENTID)
  begin
    declare @ISCONSTITUENTWHENCODE tinyint;
    select @ISCONSTITUENTWHENCODE = ISCONSTITUENTWHENCODE from dbo.STUDENTCONSTITUENCYSETTINGS;
                        
    if (@ISCONSTITUENTWHENCODE = 0) -- When student

    begin
      update dbo.CONSTITUENT
          set ISCONSTITUENT = 1,
              CHANGEDBYID = inserted.CHANGEDBYID,
              DATECHANGED = GetDate()
      from inserted
      where (CONSTITUENT.ID = inserted.CONSTITUENTID) and
              (CONSTITUENT.ISCONSTITUENT = 0) and
              (dbo.UFN_CONSTITUENT_ISSTUDENT(CONSTITUENT.ID) = 1);
    end
    else if (@ISCONSTITUENTWHENCODE = 1) -- When Alumni

    begin
      update dbo.CONSTITUENT
          set ISCONSTITUENT = 1,
              CHANGEDBYID = inserted.CHANGEDBYID,
              DATECHANGED = GetDate()
      from inserted
      where (CONSTITUENT.ID = inserted.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
        INSERTED on RELATIONCONSTITUENCIES.RECIPROCALCONSTITUENTID = INSERTED.CONSTITUENTID
    where 
        RELATIONCONSTITUENCIES.CONSTITUENTID = CONSTITUENT.ID
        and CONSTITUENT.ISCONSTITUENT = 0
  end
end