create trigger title_u_trig
  on title
  for update
  as
    declare @linkform varchar(255), @backlink int, @insert_at_end bit,
            @bib# int, @old_form varchar(255), @old_backlink int,
            @form varchar(255), @linkbib# int, @nsame smallint,
            @maxform varchar(255), @num_rows int
    select @num_rows = @@rowcount
    if update(backlink)
      /* allow update if only thing changing is backlink.  So utility "BackLink"
         can run without dropping the trigger. */
      if update(bib#) or update(processed) or update(reconst)
        begin
          raiserror 20001 "backlinks are maintained by triggers and cannot be updated otherwise"
          rollback tran
  
          return
        end
    /* reset backlink */
    if update (processed) and @num_rows != 0
      begin
        if @num_rows > 1
          begin
            raiserror 20001 "Can only maintain backlinks for one row at a time."
            rollback tran
            return
          end


        select @form = processed, @bib# = bib#
          from inserted
        select @old_form = processed, @old_backlink = backlink
          from deleted
        set rowcount 1


        /****** delete old backlink  ******/
        if exists (select * from title
                     where processed = @old_form
                       and bib# > @bib#)
          begin
            select @linkform = processed, @linkbib# = bib#
              from title
              where  
 processed = @old_form
                and bib# > @bib#
            update title set backlink = @old_backlink
              where processed = @linkform
                and bib# = @linkbib#
          end
        else
          if exists (select * from title 
  where processed > @old_form)
            begin
              select @linkform = processed, @linkbib# = bib#
                from title
                where processed > @old_form
              update title set backlink = @old_backlink
                where processed = @linkform
                  and bib# = @linkbib#
            end
        /****** insert new backlink ******/
        select @insert_at_end = 0, @backlink = 0
        if exists (select * from title
                     where processed = @form
                       and bib# > @bib#)
          select @linkform = processed, @linkbib# = bib#, @backlink = backlink
            from title
            where processed = @form
              and bib# > @bib#
        else
          begin
            if 
  exists (select * from title
                         where processed > @form)
              select @linkform = processed, @linkbib# = bib#, @backlink = backlink
                from title
                where processed > @form
            else
          
      begin
                select @insert_at_end = 1
                select @nsame = count(*) from title where processed = @form
                if @nsame = 1
                  begin
                    select @maxform = max(processed)
                    
    from title
                      where processed > (select max(processed) from title
                                           where processed < @form)
                        and processed < @form
                    if @maxform is null
              
          select @maxform = max(processed)
                        from title
                        where processed < @form
                    select @backlink = max(bib#)
                      from title
                      where processed = @maxform 
 
                  end
                else
                  select @backlink = max(bib#)
                    from title
                    where processed = @form
                      and bib# < @bib#
              end
          end
        if @backlink is null
          select @backlink = 0
        if @backlink != @bib#   /* @backlink will equal @bib# if you
                                   update a row to a value greater
                                   than it was but not great enough
          
                           to move it, in which case the
                                   backlink has already been updated
                                   properly by the "delete old
                                   backlink" code, above */
        
    update title set backlink = @backlink
            where processed = @form
              and bib# = @bib#
        if @insert_at_end = 0
          update title set backlink = @bib#
            where processed = @linkform
              and bib# = @linkbib#
      end

/*  BEGIN CUSTOM CODE FOR PRL_TITLE TABLE */
/* declare all needed variables */

   declare  @processed      varchar(255),
            @reconst        varchar(255),
            @reconstructed  varchar(255),
            @reconst_pos    int,
            @new_char       int,
           @processed_pos  int,
           @reconstructed_pos  int,
           @reconst_len    int,
           @processed_len  int,
           @reconstructed_len   int,
           @achar          char(1),
           @before_char    char(1),
           @cur_char_value int,
           @next_char      int,
           @before_num     int,
           @after_char     char(1),
           @after_num      int

	select @processed = processed from inserted where bib# = @bib#
	select @reconst = reconst from inserted where bib# = @bib#
	select @reconstructed = NULL

    /* set positional variables */
      select @reconst_pos = 1
      select @processed_pos = 0
    /* calculate length of processed and reconst columns */
      select @reconst_len = datalength(@reconst)
      select @processed_len = datalength(@processed)
      select @reconstructed = @processed
    /* start checking the reconst data one char at a time */
    while @reconst_pos < @reconst_len
      begin
    /* next character */
        select @reconst_pos = @reconst_pos + 1
    /* set current, previous and next characters (used in most reconst calculations)
    */
        select @achar = substring(@reconst,@reconst_pos,1)
        select @before_char = substring(@reconst,@reconst_pos-1,1)
        select @before_num  = ascii(@before_char)
        select @after_char  = substring(@reconst,@reconst_pos+1,1)
        select @after_num   = ascii(@after_char)
    /* start checking for the action characters I, M, R, 2, P, E, D */
    /* insert one character */
        if @achar = "I"
           begin
             select @reconstructed =   substring(@reconstructed, 1, @before_num - 1)
                                     + @after_char
                                     + substring(@reconstructed, @before_num, 255)
          /* increment position in reconst to jump past characters we have just used */
             select @reconst_pos = @reconst_pos + 2
             continue
           end
    /* insert many characters */
        if @achar = "M"
          begin
            select @reconstructed =   substring(@reconstructed, 1, @before_num - 1)
                                    + substring(substring(@reconst, @reconst_pos + 2, @after_num) + space(@after_num), 1, @after_num)
                                    + substring(@reconstructed, @before_num, 255)
         /* increment position in reconst to jump past characters we have just used */
            select @reconst_pos = @reconst_pos + 2 + @after_num
            continue
          end
    /* Replace */
        if @achar = "R"
          begin
            if @after_char is NULL
              select @after_char = " "
            select @reconstructed =   substring(@reconstructed, 1, @before_num - 1)
                                    + @after_char
                                    + substring(@reconstructed, @before_num + 1, 255)
         /* increment position in reconst to jump past characters we have just used */
            select @reconst_pos = @reconst_pos + 2
            continue
          end
    /* replace 2 */
        if @achar = "2"
          begin
            if @after_char = NULL
              select @after_char = " "
            select @reconstructed =   substring(@reconstructed, 1, @before_num - 1)
                                    + @after_char
                                    + substring(@reconstructed, @before_num + 2, 255)
         /* increment position in reconst to jump past characters we have just used */
            select @reconst_pos = @reconst_pos + 2
            continue
          end
    /* ReplacePair */
        if @achar = "P"
          begin
            select @reconstructed =   substring(@reconstructed, 1, @before_num - 1)
                                    + substring(substring(@reconst, @reconst_pos+1, 2) + "  ", 1, 2)
                                    + substring(@reconstructed, @before_num + 1, 255)
         /* increment position in reconst to jump past characters we have just used */
            select @reconst_pos = @reconst_pos + 3
            continue
          end
    /* delete */
        if @achar = "E"
          begin
            select @reconstructed =   substring(@reconstructed, 1, @before_num - 1)
                                    + substring(@reconstructed, @before_num + 1, 255)
         /* increment position in reconst to jump past characters we have just used */
            select @reconst_pos = @reconst_pos + 1
            continue
          end
    /* delete many */
        if @achar = "D"
          begin
            select @reconstructed =   substring(@reconstructed, 1, @before_num - 1)
                                    + substring(@reconstructed, @before_num + @after_num, 255)
         /* increment position in reconst to jump past characters we have just used */
            select @reconst_pos = @reconst_pos + 2
            continue
          end
    /* Clobber */
        if @achar = "C"
          begin
            select @reconstructed =   substring(@reconstructed, 1, @before_num - 1)
                                    + substring(@reconst, @reconst_pos+1, 255)
         /* increment position in reconst to jump past characters we have just used */
            select @reconst_pos = @reconst_len
            continue
          end
    /* end of while loop */
    end

/* Take out MARC non-filing indicators */
    if  charindex(char(136), @reconstructed) = 1 
        begin
            set @reconstructed = substring(@reconstructed, 2, 255)
            set @reconstructed = stuff(@reconstructed, charindex(char(137), @reconstructed), 1, NULL)
        end

/* Insert diacritics as appropriate */

    /* Set positional variables */
        select @reconstructed_pos = 0
    
    /* Calculate length of reconstructed variable */
        select @reconstructed_len = datalength(@reconstructed)

    /* Start checking the reconstructed data one character
        at a time
    */
        while @reconstructed_pos < @reconstructed_len
            begin

    /* next character */
            select @reconstructed_pos = @reconstructed_pos + 1

    /* set current and next characters (used in most diacritics) */
            select @cur_char_value = ascii(substring(@reconstructed, @reconstructed_pos, 1))
            select @next_char = ascii(substring(@reconstructed, @reconstructed_pos+1, 1))

    /* if it's a standard text character, do nothing */
            if @cur_char_value < 128
                begin
                    continue
                end

    /* if it's char(225) add a grave accent */
        if @cur_char_value = 225
            begin
                select @new_char = 
                    case @next_char
                        when  97 then 133 /* a */
                        when 101 then 138 /* e */
                        when 105 then 141 /* i */
                        when 111 then 149 /* o */
                        when 117 then 151 /* u */
                        when  65 then 183 /* A */
                        when  69 then 212 /* E */
                        when  73 then 222 /* I */
                        when  79 then 227 /* letter O */
                        when  85 then 235 /* U */
                        else @next_char
                    end
                select @reconstructed = stuff(@reconstructed, @reconstructed_pos, 2, char(@new_char))
                select @reconstructed_len = @reconstructed_len - 1
                continue
            end

    /* if it's char(226) add an acute accent  */
        if @cur_char_value = 226
            begin
                select @new_char = 
                    case @next_char
                        when  97 then 160 /* a */
                        when 101 then 130 /* e */
                        when 105 then 161 /* i */
                        when 111 then 162 /* o */
                        when 117 then 163 /* u */
                        when 121 then 236 /* y */
                        when  65 then 181 /* A */
                        when  69 then 144 /* E */
                        when  73 then 214 /* I */
                        when  79 then 224 /* letter O */
                        when  85 then 233 /* U */
                        when  89 then 237 /* Y */
                        else @next_char
                    end
                select @reconstructed = stuff(@reconstructed, @reconstructed_pos, 2, char(@new_char))
                select @reconstructed_len = @reconstructed_len - 1
                continue
            end

    /* if it's char(227) add a circumflex  */
        if @cur_char_value = 227
            begin
                select @new_char = 
                    case @next_char
                        when  97 then 131 /* a */
                        when 101 then 136 /* e */
                        when 105 then 140 /* i */
                        when 111 then 147 /* o */
                        when 117 then 150 /* u */
                        when  65 then 182 /* A */
                        when  69 then 210 /* E */
                        when  73 then 215 /* I */
                        when  79 then 226 /* letter O */
                        when  85 then 234 /* U */
                        else @next_char
                    end
                select @reconstructed = stuff(@reconstructed, @reconstructed_pos, 2, char(@new_char))
                select @reconstructed_len = @reconstructed_len - 1
                continue
            end

    /* if it's char(228) add a tilde */
        if @cur_char_value = 228
            begin
                select @new_char = 
                    case @next_char
                        when  97 then 198 /* a */
                        when 110 then 164 /* n */
                        when 111 then 228 /* o */
                        when  65 then 199 /* A */
                        when  78 then 165 /* N */
                        when  79 then 229 /* letter O */
                        else @next_char
                    end
                select @reconstructed = stuff(@reconstructed, @reconstructed_pos, 2, char(@new_char))
                select @reconstructed_len = @reconstructed_len - 1
                continue
            end

    /* if it's char(232) add a dieresis or umlaut */
        if @cur_char_value = 232
            begin
                select @new_char = 
                    case @next_char
                        when  97 then 132 /* a */
                        when 101 then 137 /* e */
                        when 105 then 139 /* i */
                        when 111 then 148 /* o */
                        when 117 then 129 /* u */
                        when 121 then 152 /* y */
                        when  65 then 142 /* A */
                        when  69 then 211 /* E */
                        when  73 then 216 /* I */
                        when  79 then 153 /* letter O */
                        when  85 then 154 /* U */
                        else @next_char
                    end
                select @reconstructed = stuff(@reconstructed, @reconstructed_pos, 2, char(@new_char))
                select @reconstructed_len = @reconstructed_len - 1
                continue
            end

    /* if it's char(234) add an angstrom (ring above) */
        if @cur_char_value = 234
            begin
                select @new_char = 
                    case @next_char
                        when  97 then 134 /* a */
                        when  65 then 143 /* A */
                        else @next_char
                    end
                select @reconstructed = stuff(@reconstructed, @reconstructed_pos, 2, char(@new_char))
                select @reconstructed_len = @reconstructed_len - 1
                continue
            end

    /* if it's char(240) add a cedilla */
        if @cur_char_value = 240
            begin
                select @new_char = 
                    case @next_char
                        when  99 then 135 /* c */
                        when  67 then 128 /* C */
                        else @next_char
                    end
                select @reconstructed = stuff(@reconstructed, @reconstructed_pos, 2, char(@new_char))
                select @reconstructed_len = @reconstructed_len - 1
                continue
            end


    /* if it's a special character, replace it with the right char */
        select @new_char = 
            case @cur_char_value
                when 162 then 157 /* uppercase slash letter o */
                when 163 then 209 /* uppercase D with crossbar */
                when 164 then 232 /* uppercase thorn */
                when 165 then 146 /* uppercase digraph AE */
                when 170 then 169 /* registered symbol r in circle */
                when 171 then 241 /* plus/minus sign */
                when 178 then 155 /* lowercase slash letter o */
                when 180 then 231 /* lowercase thorn */
                when 181 then 145 /* lowercase digraph ae */
                when 185 then 156 /* British pound symbol */
                when 186 then 208 /* lowercase eth */
                when 192 then 248 /* degree symbol */
                when 195 then 184 /* copyright symbol c in circle */
                when 199 then 225 /* lowercase sharp s (eszett) */
                else @cur_char_value
            end
        if @new_char != @cur_char_value
            select @reconstructed = stuff(@reconstructed, @reconstructed_pos, 1, char(@new_char))
        continue
	end


    update prl_title set title_reconstructed = @reconstructed where bib# = @bib#

/* END CUSTOM CODE FOR PRL_TITLE TABLE */

 return

go