TR_CONSTITUENT_UPDATE_GENDERCODE

          create trigger [dbo].[TR_CONSTITUENT_UPDATE_GENDERCODE] on [dbo].[CONSTITUENT] after update not for replication
          as begin
            declare @CHANGEAGENTID uniqueidentifier, @UNIQUEID uniqueidentifier = newid();

            -- For audit reasons:

            -- Use the same ChangeAgentId used to update the constituent row if it was updated.

            -- Since this trigger may be hit by a bulk update, we can not assume that all ChangeByIds are the same for all records unless they aren't being specified.

            if update(CHANGEDBYID)
              set @CHANGEAGENTID = null;
            else
              exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

            update dbo.CONSTITUENT
            set
              CONSTITUENT.GENDERCODE = case when inserted.GENDERCODEID is null and deleted.GENDERCODEID is not null then 0 else isnull(GENDERCODEDEFAULTMAPPING.DEFAULTREASONCODE,3) end,
              DATECHANGED = getdate(),
              CHANGEDBYID = isnull(@CHANGEAGENTID, inserted.CHANGEDBYID)
            from inserted
              inner join deleted on inserted.ID = deleted.ID and isnull(inserted.GENDERCODEID,@UNIQUEID) <> isnull(deleted.GENDERCODEID,@UNIQUEID)
              inner join dbo.CONSTITUENT on inserted.ID = CONSTITUENT.ID
              left join dbo.GENDERCODEDEFAULTMAPPING on inserted.GENDERCODEID = GENDERCODEDEFAULTMAPPING.GENDERCODEID
              where CONSTITUENT.ISORGANIZATION=0 and CONSTITUENT.ISGROUP=0
          end