Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Trigger Tablename
- Thread starter RevsEd
- Start date
SantaMufasa
Technical User
Revs,
Just a thought: Since your trigger can be for one table only, why not just hard code the name of the table where you need it in the trigger code?
![[santa]](/data/assets/smilies/santa.gif "[santa] [santa]")
Mufasa
(aka Dave of Sandy, Utah, USA @ 10:57 (22Jan04) GMT, 03:57 (22Jan04) Mountain Time)
Just a thought: Since your trigger can be for one table only, why not just hard code the name of the table where you need it in the trigger code?
Mufasa(aka Dave of Sandy, Utah, USA @ 10:57 (22Jan04) GMT, 03:57 (22Jan04) Mountain Time)
- Thread starter
- #3
SantaMufasa
Technical User
Rev,
Perhaps it is the late hour here (04:30 a.m.) that is causing my understanding to go fuzzy, but...Although I can understand (and agree with) your wanting to avoid coding 200 different procedures, I cannot understand how you can avoid coding 200 different triggers, since a trigger can be fired by ONE TABLE ONLY. If each of your 200 tables are firing a trigger, each of the 200 tables must have its own trigger. Then in the 200 triggers, you certainly can call a single, common procedure, to which you pass as a parameter the LITERAL name of the trigger-firing table, right?
If I am missing something obvious here, I promise I will not be offended if you show me what a simpleton I am.
Mufasa
(aka Dave of Sandy, Utah, USA @ 11:26 (22Jan04) GMT, 04:26 (22Jan04) Mountain Time)
Perhaps it is the late hour here (04:30 a.m.) that is causing my understanding to go fuzzy, but...Although I can understand (and agree with) your wanting to avoid coding 200 different procedures, I cannot understand how you can avoid coding 200 different triggers, since a trigger can be fired by ONE TABLE ONLY. If each of your 200 tables are firing a trigger, each of the 200 tables must have its own trigger. Then in the 200 triggers, you certainly can call a single, common procedure, to which you pass as a parameter the LITERAL name of the trigger-firing table, right?
If I am missing something obvious here, I promise I will not be offended if you show me what a simpleton I am.
Mufasa(aka Dave of Sandy, Utah, USA @ 11:26 (22Jan04) GMT, 04:26 (22Jan04) Mountain Time)
- Thread starter
- #5
Hi Mufasa,
I can see exactly what you're saying and you are correct. However, we're using Designer to do the design and we've a PL/SQL block in there which is used to generate the triggers. We been able to use the exact same bit of code so far, but now need to be able to get at the table name. Now, rather than us altering every trigger (although we probably will have to), we'd like a standard bit of code that retrieves the name and is passed to a single procedure for processing. Although we do have 200 triggers it'd be nice if the code was duplicate so I don;t have to do it 200 times.... it's a nice theory anyway.
Whatever - what I really need is a way to tell within a trigger, which table fired it.
Thanks again for you input
Darren
I can see exactly what you're saying and you are correct. However, we're using Designer to do the design and we've a PL/SQL block in there which is used to generate the triggers. We been able to use the exact same bit of code so far, but now need to be able to get at the table name. Now, rather than us altering every trigger (although we probably will have to), we'd like a standard bit of code that retrieves the name and is passed to a single procedure for processing. Although we do have 200 triggers it'd be nice if the code was duplicate so I don;t have to do it 200 times.... it's a nice theory anyway.
Whatever - what I really need is a way to tell within a trigger, which table fired it.
Thanks again for you input
Darren
Your will still need to modify the 200 triggers to add what ever is required to obtain the table name (if there is such a method).
If you are using a PL/SQL block to generate your triggers, the PL/SQL block generating the triggers knows the name of the tables for which it is generating the triggers (since it has to generate: CREATE OR REPLACE TRIGGER tablename). So the the PL/SQL block should be able to also generate the call to the common procedure with an argument that contains the table name.
If you are using a PL/SQL block to generate your triggers, the PL/SQL block generating the triggers knows the name of the tables for which it is generating the triggers (since it has to generate: CREATE OR REPLACE TRIGGER tablename). So the the PL/SQL block should be able to also generate the call to the common procedure with an argument that contains the table name.
- Thread starter
- #7
Yes I know I have to still have to alter the triggers.
I am not using a PL/SQL Block to generate the triggers (although I misleadingly said I was). What I meant was that in designer we have a "single" PL/SQL Block which is inserted into every trigger. We do not need to change this single block of code. Now, if we code the block to somehow refer to the trigger which it will be put in, then I don't need to rewrite the code from the trigger 200 times.
Thanks for taking an interest - though it is increasinly looking like there's no way to determine the table which has fired it.
Darren
I am not using a PL/SQL Block to generate the triggers (although I misleadingly said I was). What I meant was that in designer we have a "single" PL/SQL Block which is inserted into every trigger. We do not need to change this single block of code. Now, if we code the block to somehow refer to the trigger which it will be put in, then I don't need to rewrite the code from the trigger 200 times.
Thanks for taking an interest - though it is increasinly looking like there's no way to determine the table which has fired it.
Darren
- Status