CODEWITHSUNDEEP
sqloracle-databaseplsqldatabase-trigger
Emre Öztürk
Emre Öztürk

Reputation: 11

How to make :NEW dynamic PL/SQL

This is my trigger code. But I want to dynamically reference :new attributes without writing them one by one. How can I do that?

create or replace TRIGGER test_CHANGE_TRIGGER
  AFTER INSERT OR UPDATE OR DELETE
  ON test
  FOR EACH ROW
BEGIN
   IF INSERTING THEN
      INSERT INTO test_LOG 
      VALUES (:NEW.d, :NEW.s, :NEW.n, :NEW.v, :NEW.e, SYSDATE, USER, 'I');
   END IF;
END;

Upvotes: 0

Views: 185

Answers (2)

Koen Lostrie
Koen Lostrie

Reputation: 18685

You could write a sql statement to generate the insert statement for you. Copy the result and paste it in the trigger code. Example for table emp:

SELECT 
'INSERT INTO emp_log(empno,ename, job, mgr, hiredate, sal, comm, deptno, log_date, user, operation) VALUES ('||
':NEW.'||LISTAGG(column_name,' ,:NEW.') WITHIN GROUP(order by column_id)||',SYSDATE,USER,''I'')' 
 FROM user_tab_columns 
WHERE table_name = 'EMP';

This will return 

INSERT INTO emp_log(empno,ename, job, mgr, hiredate, sal, comm, deptno, log_date, user, operation) VALUES (:NEW.EMPNO ,:NEW.ENAME ,:NEW.JOB ,:NEW.MGR ,:NEW.HIREDATE ,:NEW.SAL ,:NEW.COMM ,:NEW.DEPTNO,SYSDATE,USER,'I')

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142798

Lucky you, the answer is pretty straightforward: you can't do that, and you have to name all values one-by-one.

Unfortunately, there's nothing (provided by Oracle) you could use for that purpose.

Though, you could try to create your own procedure which would use dynamic SQL and create triggers for you (query user_tab_columns to get list of all columns). Dynamic SQL doesn't scale well, is difficult to debug, but - if you think that it'll help (because there are hundreds of triggers you have to create) - go for it.

Upvotes: 0

Related Questions