assing all OLD columns to NEW in oracle trigger-CodePudding

I have a requirement where , before update - if my sequence is not changed - then i will update only STATUS of my record and will ignore all other attributes .

MY_TABLE has 30 columns - and after every few months - a column gets added to support a new feature - i didn't wanted to add mapping for every column.

My trigger syntax

 CREATE OR REPLACE  TRIGGER MY_TABLE_TRIGGER BEFORE UPDATE
    ON
    MY_TABLE
FOR EACH ROW

DECLARE

STATUS varchar2(10);

BEGIN

  
    if ( :NEW.SEQ_ID >= :OLD.SEQ_ID  ) then
    
    STATUS := :NEW.STATUS

    -- this does not work
    :NEW.* := :OLD.* ;
    -- this does not work
    NEW := OLD ;
    
    :NEW.STATUS = STATUS ; 

    
    END if;


END MY_TABLE_TRIGGER;
/

What can i do - so that I can replace NEW with OLD valuse ( all columns ) and then update NEW with only STATUS.

Thank you in advance.

CodePudding user response：

This cannot be done, it is very well explained why in the answer on this question. The easiest alternative is to generate the pl/sql for you whenever there is a change - copy this into the trigger body and compile. Something like this:

select ':NEW.'||COLUMN_NAME||' := :OLD.'||COLUMN_NAME||';' 
  from user_tab_columns 
 where table_name = 'MY_TABLE'
   and column_name != 'STATUS';

Which for the EMP table generates this output :

:NEW.EMPNO := :OLD.EMPNO;                            
:NEW.ENAME := :OLD.ENAME;                            
:NEW.JOB := :OLD.JOB;                                
:NEW.MGR := :OLD.MGR;                                
:NEW.HIREDATE := :OLD.HIREDATE;                      
:NEW.SAL := :OLD.SAL;                                
:NEW.COMM := :OLD.COMM;                              
:NEW.DEPTNO := :OLD.DEPTNO;

If this is a very common task you could create a script to generate and compile the complete trigger.

CodePudding user response：

MY_TABLE has 30 columns - and after every few months - a column gets added to support a new feature - i didn't wanted to add mapping for every column.

That's exactly where bad habits kick. When you make a wrong decision at the beginning, you have to suffer later.

I suggest you throw that data model into garbage and do it from scratch, but this time normalize it to 3NF and your life will be MUCH EASIER from now on.

Because, whatever you try to do now to save yourself of misery, it'll still be a bad model.