Home » SQL & PL/SQL » SQL & PL/SQL » Creating Trigger with Dynamic sql when ever table structure changes
Creating Trigger with Dynamic sql when ever table structure changes [message #379756] Wed, 07 January 2009 19:58 Go to next message
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 #379762 is a reply to message #379756] Wed, 07 January 2009 20:32 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
You have not read or followed Posting Guideline as stated in URL above.

Please do NOT multi/cross-post

>Here my concern is i don't want to edit the trigger mnually..
>please give me assistance to achive the above requirement...
Write a trigger to re-write desired triggers after DDL changes.
Re: Creating Trigger with Dynamic sql when ever table structure changes [message #379774 is a reply to message #379756] Wed, 07 January 2009 23:05 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:59412348055
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 Go to previous messageGo to next message
Frank
Messages: 7880
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.
Re: Creating Trigger with Dynamic sql when ever table structure changes [message #379948 is a reply to message #379774] Thu, 08 January 2009 10:20 Go to previous messageGo to next message
ldhullipalla
Messages: 4
Registered: January 2009
Location: Reston
Junior Member
Hi Barbara Boehmer,

Thanks for quick responce,I had a walkthrough the link which you sent,i didn't find the solution for my requirment..

>>My concern is when ever table altered by adding column or deleting,Triggers based on that table should re-create using dynamic sql..

Thanks for your Assistance..
Re: Creating Trigger with Dynamic sql when ever table structure changes [message #379949 is a reply to message #379948] Thu, 08 January 2009 10:25 Go to previous message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You didn't read carefully the link.
Try again and explain why it does not give you or lead you to a solution.

Regards
Michel
Previous Topic: What is wrong with this trigger code? (merged)
Next Topic: Records between current date and 2 weeks from the current date?
Goto Forum:
  


Current Time: Fri Dec 09 03:44:45 CST 2016

Total time taken to generate the page: 0.24773 seconds