Creating Trigger with Dynamic sql when ever table structure changes [message #379756] |
Wed, 07 January 2009 19:58  |
ldhullipalla
Messages: 4 Registered: January 2009 Location: Reston
|
Junior Member |
|
|
Hi,
I need to automate the trigger code when ever the dependent Table structure altered.
Example
If my table is having 6 columns like
empno,ename,job,Hiredate,sal,comm
In the Audit trigger it is inserting like
after update on emp
insert into emp_audit(empno,ename,job,hiredate,sal,comm) values(:old.empno,:old.ename,:old.job,:old.Hiredate,:old.sal,:old.comm)
After a while we Altered the emp table by Adding two more columns
now the Audit Trigger should insert like
insert into emp_audit(empno,ename,job,hiredate,sal,comm,deptno,Address) values(:old.empno,:old.ename,:old.job,:old.Hiredate,:old.sal,:old.comm,:old.deptno,:old.Address)
Here my concern is i don't want to edit the trigger mnually..
I am trying to achive with
re-creating the trigger with Dynamic sql based on the user_tab_columns..
please give me assistance to achive the above requirement...
Thanks
Lakshman
|
|
|
|
|
Re: Creating Trigger with Dynamic sql when ever table structure changes [message #379832 is a reply to message #379756] |
Thu, 08 January 2009 01:34   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
How often do you add columns to your datamodel? This should not be a regular thing, so rewriting the audit-trigger should be far less work than creating a complete framework to keep them up-to-date.
Automation is good, being lazy is not.
Adding a column to a table means you have to do an impact analysis on your complete code-base to see what else needs to be changed. Well, this single trigger is one of them. The audit table is another.
|
|
|
|
|