Re: TRIGGER-Name of the current table

From: Martin T. <bilbothebagginsbab5_at_freenet.de>
Date: 5 Oct 2006 08:56:48 -0700
Message-ID: <1160063808.710857.209810@b28g2000cwb.googlegroups.com>

Robbert van der Hoorn wrote:
> 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...

Copy&Paste is the single most evil thing I keep stubling over in sourcecode. It annoys me without end that (PL/)SQL makes it easy to solve things via c&p and makes it often amazingly hard to solve things The Right Way (which is never c&p as far as I'm concerned).

The code generation script is a rather decent way around this, since you don't maintain code n times, but instead maintain just one generation script. Still it would be nice if one wouldn't be forced to use it to begin with.

Anyway, I heartedly agree about TEST :-)

cheers,
Martin Received on Thu Oct 05 2006 - 10:56:48 CDT