Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic Generation of Trigger Code (Oracle 10g)
Dynamic Generation of Trigger Code [message #322402] Fri, 23 May 2008 06:38 Go to next message
thisisnatesh
Messages: 92
Registered: March 2007
Member
Hi Everybody

I have a requirement.

Given below is existing trigger code

CREATE OR REPLACE TRIGGER TRIG1
after update on emp
for each row
v_index integer:=0;
affecting_col,oldvalue,newvalue declarations..
begin
IF (:NEW.emp_id_n IS NULL )  THEN
vIndex:=vIndex+1;
  affecting_col(vIndex):='EMP_ID_N';
  oldvalue(vIndex):=:OLD.EMP_ID_N;
  newvalue(vIndex):=:NEW.EMP_ID_N;
END IF;
end;



My Requirement is when emp table is added with
new column, automatically the trigger code
has to be replaced with existing code and
added with a new if block.

when a new column ename is added to emp table
It should look like

CREATE OR REPLACE TRIGGER TRIG1
after update on emp
for each row
v_index integer:=0;
affecting_col,oldvalue,newvalue declarations..
begin
IF (:NEW.emp_id_n IS NULL )  THEN
vIndex:=vIndex+1;
  affecting_col(vIndex):='EMP_ID_N';
  oldvalue(vIndex):=:OLD.EMP_ID_N;
  newvalue(vIndex):=:NEW.EMP_ID_N;
END IF;

IF (:NEW.ENAME_C IS NULL )  THEN
vIndex:=vIndex+1;
  affecting_col(vIndex):='ENAME_C';
  oldvalue(vIndex):=:OLD.ENAME_C;
  newvalue(vIndex):=:NEW.ENAME_C;
END IF;

end;


How should i proceed to attain this
type of automation?

Please help me in this regard and
message for any clarifications.

Thanks in Advance
Natesh
Re: Dynamic Generation of Trigger Code [message #322404 is a reply to message #322402] Fri, 23 May 2008 06:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
AskTom generic trigger for auditing column level changes

Regards
Michel
Re: Dynamic Generation of Trigger Code [message #322408 is a reply to message #322404] Fri, 23 May 2008 06:49 Go to previous message
thisisnatesh
Messages: 92
Registered: March 2007
Member
Thanks Mic for the info
Previous Topic: uutl_file error
Next Topic: Partitioning an existing table
Goto Forum:
  


Current Time: Tue Dec 06 12:15:51 CST 2016

Total time taken to generate the page: 0.11882 seconds