Upgrading the trigger in the product database on PostgreSQL

The following listing shows the out-of-the-box trigger code with the highlighted lines that must be added to enable it to populate the Master UID column. Apply only the suggested modifications without changing the base code of the trigger.

CREATE OR REPLACE FUNCTION iga_core."F_TRG_USER_ERC_TO_PM"()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
declare

  var_operation       event_in.operation%type;
  var_ext_table       event_in.ext_table%type;
   var_pm_code         event_in.pm_code%type; 
  var_columns_changed event_in.ext_attr9%type;
  ou_oper             numeric(16);

  var_ou              user_erc.ou%type;
  var_action_type     user_erc.action_type%type;
  var_action_cause    user_erc.action_cause%type;
begin

  if (trim(upper(user)) = trim(upper('IDEAS'))) then
    if (TG_OP = 'INSERT' or TG_OP = 'UPDATE') then
      return new;
    elsif (TG_OP = 'DELETE') then
      return old;
    end if;
  end if;

  -- Default mapping from USER_ERC to EVENT_IN
  --
  -- ext_table.ou ---> event_in.ext.attr2
  -- ext_table.action_type ---> event_in.ext.attr3
  -- ext_table.action_cause ---> event_in.ext.attr4
  -- var_columns_changed ---> event_in.ext.attr9

  -- var_columns_changed contains the columns changed name separated by ;
  -- columns monitored: PM_CODE;GIVEN_NAME;SURNAME;EMAIL;USER_TYPE;DISABLED;DELETED;ATTR1;ATTR2;ATTR3;ATTR4;ATTR5;

  -- ********************* insert ********************* --
  if (TG_OP = 'INSERT') then
    var_operation := 1;
    if ( new.schedule = 1 ) then
      var_operation := var_operation + 100;
    end if;

    var_ext_table    := new.id;
     var_pm_code      := new.pm_code; 
    var_ou           := new.ou;
    var_action_type  := new.action_type;
    var_action_cause := new.action_cause;
    -- ********************* update ********************* --
  elsif (TG_OP = 'UPDATE') then
    var_operation := 2;
    ou_oper := 12;
    if ( new.schedule = 1 ) then
      var_operation := var_operation + 100;
      ou_oper       := ou_oper + 100;
    end if;

    var_ext_table    := new.id;
     var_pm_code		 := new.pm_code; 
    var_ou           := new.ou;
    var_action_type  := new.action_type;
    var_action_cause := new.action_cause;

    -- calculate var_columns_changed
    var_columns_changed := '';
    if ((old.PM_CODE != new.PM_CODE) or
       (old.PM_CODE is null and new.PM_CODE is not null) or
       (old.PM_CODE is not null and new.PM_CODE is null)
       ) then
      var_columns_changed := 'PM_CODE;';
    end if;
    if ((old.GIVEN_NAME != new.GIVEN_NAME) or
       (old.GIVEN_NAME is null and new.GIVEN_NAME is not null) or
       (old.GIVEN_NAME is not null and new.GIVEN_NAME is null)
       ) then
      var_columns_changed := var_columns_changed || 'GIVEN_NAME;';
    end if;
    if ((old.SURNAME != new.SURNAME) or
       (old.SURNAME is null and new.SURNAME is not null) or
       (old.SURNAME is not null and new.SURNAME is null)
       ) then
      var_columns_changed := var_columns_changed || 'SURNAME;';
    end if;
    if ((old.EMAIL != new.EMAIL) or
       (old.EMAIL is null and new.EMAIL is not null) or
       (old.EMAIL is not null and new.EMAIL is null)
       ) then
      var_columns_changed := var_columns_changed || 'EMAIL;';
    end if;
    if ((old.USER_TYPE != new.USER_TYPE) or
       (old.USER_TYPE is null and new.USER_TYPE is not null) or
       (old.USER_TYPE is not null and new.USER_TYPE is null)
       ) then
      var_columns_changed := var_columns_changed || 'USER_TYPE;';
    end if;
    if ((old.DISABLED != new.DISABLED) or
       (old.DISABLED is null and new.DISABLED is not null) or
       (old.DISABLED is not null and new.DISABLED is null)
       ) then
      var_columns_changed := var_columns_changed || 'DISABLED;';
    end if;
    if ((old.DELETED != new.DELETED) or
       (old.DELETED is null and new.DELETED is not null) or
       (old.DELETED is not null and new.DELETED is null)
       ) then
      var_columns_changed := var_columns_changed || 'DELETED;';
    end if;
    if ((old.ATTR1 != new.ATTR1) or
       (old.ATTR1 is null and new.ATTR1 is not null) or
       (old.ATTR1 is not null and new.ATTR1 is null)
       ) then
      var_columns_changed := var_columns_changed || 'ATTR1;';
    end if;
    if ((old.ATTR2 != new.ATTR2) or
       (old.ATTR2 is null and new.ATTR2 is not null) or
       (old.ATTR2 is not null and new.ATTR2 is null)
       ) then
      var_columns_changed := var_columns_changed || 'ATTR2;';
    end if;
    if ((old.ATTR3 != new.ATTR3) or
       (old.ATTR3 is null and new.ATTR3 is not null) or
       (old.ATTR3 is not null and new.ATTR3 is null)
       ) then
      var_columns_changed := var_columns_changed || 'ATTR3;';
    end if;
    if ((old.ATTR4 != new.ATTR4) or
       (old.ATTR4 is null and new.ATTR4 is not null) or
       (old.ATTR4 is not null and new.ATTR4 is null)
       ) then
      var_columns_changed := var_columns_changed || 'ATTR4;';
    end if;
    if ((old.ATTR5 != new.ATTR5) or
       (old.ATTR5 is null and new.ATTR5 is not null) or
       (old.ATTR5 is not null and new.ATTR5 is null)
       ) then
      var_columns_changed := var_columns_changed || 'ATTR5;';
    end if;

    -- Customizations ...
    --if ((old.ATTR5 != new.ATTR5) or
    --   (old.ATTR5 is null and new.ATTR5 is not null) or
    --   (old.ATTR5 is not null and new.ATTR5 is null)
    --   ) then
    --  var_columns_changed := var_columns_changed || 'ATTR5;';
    --end if;


    -- if OU changed generate a move user event before, operation=12
    if ( ( old.ou is null and new.ou is not null )
         or ( old.ou is not null and new.ou is null )
         or ( old.ou != new.ou ) ) then
      insert into event_in
        (id,
         ext_table,
          pm_code, 
         operation,
         state,
         date_process,
         last_mod_time,
         last_mod_user,
         ext_attr1,
         ext_attr2,
         ext_attr3,
         ext_attr4,
         erc)
      values
        (nextval('event_in_seq'),
         var_ext_table,
          var_pm_code, 
         ou_oper,
         0,
         now(),
         now(),
         'trg_connector_user_to_pm',
         null,
         new.ou,
         new.action_type,
         new.action_cause,
         'user_erc');
    end if; --if ( old.ou != new.ou ) then

    -- ********************* delete ********************* --
  elsif (TG_OP = 'DELETE') then
    var_operation := 3;
    if ( old.schedule = 1 ) then
      var_operation := var_operation + 100;
    end if;

    var_ext_table    := old.id;
     var_pm_code      := old.pm_code; 
    var_ou           := old.ou;
    var_action_type  := old.action_type;
    var_action_cause := old.action_cause;
  end if; --if (TG_OP = 'INSERT') then

  -- ********************* insert event ********************* --
  insert into event_in
    (id,
     ext_table,
      pm_code, 
     operation,
     state,
     date_process,
     last_mod_time,
     last_mod_user,
     ext_attr1,
     ext_attr2,
     ext_attr3,
     ext_attr4,
     ext_attr5,
     ext_attr6,
     ext_attr7,
     ext_attr8,
     ext_attr9,
     ext_attr10,
     erc)
  values
    (nextval('event_in_seq'),
     var_ext_table,
      var_pm_code, 
     var_operation,
     0,
     now(),
     now(),
     'trg_connector_user_to_agc',
     null, -- customizable
     var_ou,
     var_action_type,
     var_action_cause,
     null, -- customizable
     null, -- customizable
     null, -- customizable
     null, -- customizable
     var_columns_changed,
     null,  -- used by the user_before rule = original cod_operation event discarded
     'user_erc');

  if (not TG_OP = 'DELETE') then
    new.action_type  := '0';
    new.action_cause := '0';
  end if;

  if (TG_OP = 'INSERT' or TG_OP = 'UPDATE') then
    return new;
  elsif (TG_OP = 'DELETE') then
    return old;
  end if;
end;
$function$