PLSQL trigger: override column when row updated

Question

I have two tables: 1 with my main data (TABLE: ASSET):

ID | DESCR | TYPE1 | COUNTRY
 1 | blabla1 | A1 | AT
 2 | blabla2 | A2 | DE
 3 | blabla3 | A2 | CH

and the second table like (TABLE: ASSET_OVERRULE):

ID | OVERRULE_COLUMN | OVERRULE_VALUE
 1 | TYPE1           | A2

And now I want to have a trigger, which should check the table ASSET_OVERRULE, every time someone update something in the table ASSET. If a entry with the same ID is found, then set the new updated value to the value in the ASSET_OVERRULE.

This is the trigger atm:

DECLARE
 CURSOR assets_overrule_cur
  IS
    SELECT *
    FROM asset_overrule
    WHERE id = :NEW.id;
 sTemp VARCHAR2(255):=NULL;
 y NUMBER;
BEGIN
  FOR assets_overrule 
  IN assets_overrule_cur
  LOOP

    sTemp := ':NEW.' || assets_overrule.OVERRULE_COLUMN || ':=''' ||     assets_overrule.OVERRULE_VALUE|| ''';';

    execute immediate sTemp;     

  END LOOP;
END;

But this error appears:

Knowledge Xpert cannot be found.

I think, I need another solution to solve this problem..

Answer 1

Here's how I'd do it, if I was stuck with this data model:

drop table test_asset;

drop table test_asset_overrule;

create table test_asset (id number,
                         descr varchar2(10),
                         type1 varchar2(5),
                         country varchar2(3));

create table test_asset_overrule (id number,
                                  overrule_column varchar2(30),
                                  overrule_value varchar2(10));

insert into test_asset
select 1, 'blabla1', 'A1', 'AT' from dual union all
select 2, 'blabla2', 'A2', 'DE' from dual union all
select 3, 'blabla3', 'A2', 'CH' from dual;

insert into test_asset_overrule
select 1, 'TYPE1', 'A2' from dual;

commit;

create or replace trigger test_asset_trg 
before update on test_asset
for each row
begin
  for rec in (select id,
                     overrule_column,
                     overrule_value
              from   test_asset_overrule
              where  id = :old.id
              and    id = :new.id)
  loop
    if upper(rec.overrule_column) = 'TYPE1' then
      :new.type1 := rec.overrule_value;
    elsif upper(rec.overrule_column) = 'COUNTRY' then
      :new.country := rec.overrule_value;
    end if;
  end loop;
end test_asset_trg;
/

update test_asset
set type1 = 'A3';

commit;

select * from test_asset;

        ID DESCR      TYPE1 COUNTRY
---------- ---------- ----- -------
         1 blabla1    A2    AT     
         2 blabla2    A3    DE     
         3 blabla3    A3    CH   

Answer 2

You can't use the :new (or :old) pseudorecord in dynamic SQL like that. You'd need to use static SQL with a separate branch for each column. Something like

DECLARE
  l_override_val asset_overrule.overrule_value%type;
BEGIN
  BEGIN
    SELECT overrule_value
      INTO l_override_val
      FROM asset_overrule
     WHERE id = :new.id
       AND override_column = 'TYPE1';
    :new.type1 := l_override_val;
  EXCEPTION
    WHEN no_data_found
    THEN
      NULL;
  END;

  BEGIN
    SELECT overrule_value
      INTO l_override_val
      FROM asset_overrule
     WHERE id = :new.id
       AND override_column = 'COUNTRY';
    :new.country := l_override_val;
  EXCEPTION
    WHEN no_data_found
    THEN
      NULL;
  END;

  <<repeat for each column you want to override>>
END;

Taking a step back, the data model appears at the very least to be clunky. Why have separate rows if you want to override multiple column values rather than letting the definitions of the two tables match? Do you really need to change the values in a trigger rather than simply doing the overriding in whatever view returns the data so that if you remove the override you go back to whatever the system set the value to? Wouldn't it make sense for the application that is doing the UPDATE to check the override table and either not update those values or alert the user that those values were overridden or something like that?