Dynamic Column Update PLSQL trigger

SandeepChander 0
Viewed 16

I have two tables A and B

In table A there are two columns "Sequence Number" and "Content".

Name    Null? Type         
------- ----- ------------ 
SEQ_NO        NUMBER(6)    
CONTENT       VARCHAR2(20)

In table B there are multiple statement columns like "Stmt_1", "Stmt_2", "Stmt_3" etc.

Name   Null? Type         
------ ----- ------------ 
STMT_1       VARCHAR2(20) 
STMT_2       VARCHAR2(20) 
STMT_3       VARCHAR2(20) 
STMT_4       VARCHAR2(20)

I want to create a trigger on table A such that after every insert on table A, according to the "Sequence Number" value the corresponding column in table B gets updated.

For example: If table A has "Sequence Number" = 1 , then "Stmt_1" of table B gets updated to the value of "Content" column in table A.

If table A is given as

"SEQ_NO"    "CONTENT"
1   "This is Content"

Then Table B should look like:

"STMT_1","STMT_2","STMT_3","STMT_4"
"This is Content","","",""

My approach is as follows:

create or replace trigger TestTrig
after insert on A for each row
begin
    declare
    temp varchar2(6);
    begin
        temp := concat("Stmt_",:new.seq_no);
        update B
        set temp = :new.content;
    end;
end;

But I am getting an error in the update statement.

Does anyone know how to approach this problem?

oracle-sqldeveloper plsql triggers
1 Answers

You need to use dynamic SQL (and ' is for string literals, " is for identifiers):

create or replace trigger TestTrig
  after insert on A
  for each row
DECLARE
  temp varchar2(11);
begin
  temp := 'Stmt_' || TO_CHAR(:new.seq_no, 'FM999990');
  EXECUTE IMMEDIATE 'UPDATE B SET ' || temp || ' = :1' USING :NEW.content;
end;
/

You probably want to handle errors when seq_no is input as 5 and there is no STMT_5 column in table B:

create or replace trigger TestTrig
  after insert on A
  for each row
DECLARE
  temp varchar2(11);
  INVALID_IDENTIFIER EXCEPTION;
  PRAGMA EXCEPTION_INIT(INVALID_IDENTIFIER, -904);
begin
  temp := 'Stmt_' || TO_CHAR(:new.seq_no, 'FM999990');
  EXECUTE IMMEDIATE 'UPDATE B SET ' || temp || ' = :1' USING :NEW.content;
EXCEPTION
  WHEN INVALID_IDENTIFIER THEN
    NULL;
end;
/

However

I would suggest that you do not want a table B or a trigger to update it and you want a VIEW instead:

CREATE VIEW B (stmt_1, stmt2, stmt3, stmt4) AS
SELECT *
FROM   A
PIVOT (
  MAX(content)
  FOR seq_no IN (
    1 AS stmt_1,
    2 AS stmt_2,
    3 AS stmt_3,
    4 AS stmt_4
  )
);

fiddle;

MT00
Related