Home » SQL & PL/SQL » SQL & PL/SQL » Trigger creation in a loop
Trigger creation in a loop [message #251765] Mon, 16 July 2007 10:41 Go to next message
hli33
Messages: 3
Registered: July 2007
Junior Member
hey guys,

I want to create a trigger for each table in my database, since the number of tables are continually growing, i'd like to have a way to create these triggers inside a loop: "FOR item IN ( SQL select statement)"

But I'm having a problem creating the trigger names, is there anyway to create trigger names from variables or anything? concatenate maybe? Or should I find a completely different approach?

Thanks

Re: Trigger creation in a loop [message #251766 is a reply to message #251765] Mon, 16 July 2007 10:43 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
This certainly can be done. The question is what are you trying to accomplish ? WHy would you need a trigger on every table ?
Re: Trigger creation in a loop [message #251768 is a reply to message #251765] Mon, 16 July 2007 10:50 Go to previous messageGo to next message
hli33
Messages: 3
Registered: July 2007
Junior Member
the trigger updates a column in each table, i didn't design the database so I won't be able to explain all the details about why, but its a sort of version verification system.
Re: Trigger creation in a loop [message #251774 is a reply to message #251765] Mon, 16 July 2007 11:04 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member

You can try something like this (Depending on your naming conventions)


declare

v_sql_1  varchar2(30) := 
  'CREATE OR REPLACE TRIGGER ' ;

v_sql_2 varchar2(39) := 
  ' BEFORE INSERT OR UPDATE on ';

v_sql_3 varchar2(3800) := 'YOUR TRIGGER TEXT');

v_trigger_sql varchar2(4000);

begin

for t in (select table_name from user_tables) loop
    v_trigger_sql :=v_static_sql1||t.table_name||'B4'||v_sql2||
                    t.table_name||v_sql_3;

    execute immediate (v_trigger_sql);
end loop;
end;




**NB Totally untested, there are probably syntax errors.

Re: Trigger creation in a loop [message #251812 is a reply to message #251765] Mon, 16 July 2007 14:11 Go to previous messageGo to next message
hli33
Messages: 3
Registered: July 2007
Junior Member
wow.. excellent code. thank you very much, i've got much to learn.. haha..
Re: Trigger creation in a loop [message #251818 is a reply to message #251768] Mon, 16 July 2007 15:31 Go to previous message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
You can use execute immediate within a loop to create your triggers, as already suggested, or you can use sql to create sql, whichever you prefer. You may want to create one generic procedure, then dynamically generate one trigger per table, where each trigger calls the generic procedure. That way, if you need to make any future changes you only need to change one procedure or add another short trigger if another table is added. Here is a nice example by Tom Kyte:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:59412348055
Previous Topic: performance problems
Next Topic: No data found. Is it a bug?
Goto Forum:
  


Current Time: Sat Dec 10 22:31:45 CST 2016

Total time taken to generate the page: 0.11692 seconds