1

My colleague and I are attempting to write a database trigger in PL/SQL that can look into a mapping table to find the ID of a specific column from another table (example tables included below) and then update the value in that column.

We need to be able to do this as a BEFORE EACH ROW trigger so that we modify the data before it is committed into the database.

We ended up writing a massive CASE statement to handle this, but does anyone know of a better way to handle this sort of situation? In our real life example, we also have 70 columns to look through, rather than 8, like in the example below.

create table test_basis (
  id      number(22,0) not null,
  item_1  number(22,0) not null,
  item_2  number(22,0) not null,
  item_3  number(22,0) not null,
  item_4  number(22,0) not null,
  item_5  number(22,0) not null,
  item_6  number(22,0) not null,
  item_7  number(22,0) not null,
  item_8  number(22,0) not null
)

create table basis_mapping (
  column_id    number(22,0),
  column_name  varchar2(35)
)

insert into basis_mapping(1, 'Company');
insert into basis_mapping(2, 'Account');
insert into basis_mapping(3, 'Manager');
insert into basis_mapping(4, 'User');

For our purposes, we need to look up the column_id from basis_mapping based on the column_name, and then update the :new.item_### field in the current row. So, if we wanted to change the value in the 'User' column, we would run the following statement

select column_id 
  into l_basis_id
  from basis_mapping 
  where column_name = 'User'

Now that we have 4 in the l_basis_id variable, how can we use this to generate a statement like the following?

:new.item_4 = 0
Jared Watkins
  • 11
  • 1
  • do it via dynamic sql.......? I haven't done dynamic slq in oracle but here is a link on it https://docs.oracle.com/cd/B10501_01/appdev.920/a96590/adg09dyn.htm – Matt Jul 08 '16 at 18:50
  • When you start having lots of columns, called "item_xxx", I would say maybe, your design of "test_basis" table is flawed. Maybe you need a repeating, join table, or another column, "item_name", or something? – OldProgrammer Jul 08 '16 at 19:02
  • 1
    You database design looks like a key-value model (not recommended in relational databases) http://stackoverflow.com/questions/126271/key-value-pairs-in-relational-database, but your model is developed to a higher (much more complicated) level of abstraction. At the very last consider normalizing the table `test_basic` to three columns: `(id, column_id, item_value )` – krokodilko Jul 08 '16 at 19:30
  • 1
    There is no easy way to be this sort of dynamic assignment in PL/SQL. You should take this as a pointer that you are taking the wrong approach to whatever business problem you're tackling. Your best options are to consider an approach which works with the grain of a relational database system or stick with your monster CASE statement. – APC Jul 10 '16 at 12:57
  • Unfortunately this is a very old system and these tables are incredibly centralized, so fixing the schema to what it should be isn't really an option at this point. I do appreciate the suggestions though, but it looks like we will have to stick with the CASE statement for now. Thanks! – Jared Watkins Jul 11 '16 at 13:19

1 Answers1

0

Unfortunately, you cannot refer to :new and :old in dynamic SQL. Please have a look at Oracle PL/SQL: Loop Over Trigger Columns Dynamically.

Community
  • 1
  • 1
Krzysztof Kaszkowiak
  • 886
  • 6
  • 17