Re: TRIGGER-Name of the current table

Please don't top post. See below.
"Taras" <taras.kolakovic_at_gmail.com> schreef in bericht news:1160052829.467604.54000_at_c28g2000cwb.googlegroups.com...
> Thanx for your answer, Martin,
>
> The main goal is to pass the original (old) record to a procedure,
> which would then concatinate all the values from this record an store
> them into a logging table. (i wanted to use that table name to retrieve
> rowtype later)
>
> I would like to have only one procedure, which would log records from
> many different tables.
> (perhaps i would pass them as ref cursor, i'm not sure yet how to do
> it, since i'm a beginner)
>
> The reason is that if we would decide to change the logging mechanism
> later, we would have to change only that procedure instead of 50
> triggers accross the schema.
>
> Also i would like to have this universal code, to add it to any new
> tables without serious modifications (because our database structure
> tends to change often)
>
> Regards, Taras
>
>
>
>
> Martin T. je napisal:
>> Taras wrote:
>> > Hello,
>> >
>> > I'm fairly new at PL-SQL, so forgive mi if this question sounds
>> > stupid...
>> >
>> > I'm trying to write a trigger on a certain table. Is it possible to
>> > retrieve the name of that table within the code of it's trigger?
>> >
>> > My goal is to write a code for universal trigger (it would do the same
>> > thing on all tables). That trigger would call a procedure and pass the
>> > name of it's parent table as a parameter.
>> >
>>
>> Maybe this is a good starting point for further searches:
>> http://forums.oracle.com/forums/message.jspa?messageID=512084
>>
>> What would your "universal" trigger do?
>>
>> cheers,
>> Martin
>

No, it does not sound stupid. Many have tried this before you did. But: since you're a beginner, don't do it! (and if you were'nt, don't do it either). There are plenty standard ways of logging changes in tables, like Journal Tables. Universal Code always leads to overhead, is hard to maintain and the requested changes are always the ones your UC does not support yet. I had a colleague once, trying to write the Universal Report, independant of which tables and relationships. He tried for six months. Never heard of him again, after he was fired. Stick to proven technology, and let the experts develop and proof new technology.
If you still want some universality in your code, try to build a generator that produces your trigger code based on table name and stucture, but creates table-specific code. I might suggest to take a look at tools like Oracle Designer.
But I think copy, paste, modify (and TEST) is still the most productive way...

Robbert van der Hoorn
OSA it BV
The Netherlands Received on Thu Oct 05 2006 - 08:44:41 CDT