Re: Consequence of renaming table for triggers ?

Do this interesting experiment. Suppose you have a table and a sequence and a trigger that automatically inserts the next value of the sequence into a column of that table, a very typical use of triggers and sequences. Now you rename the table. What happens?

Answer: The trigger gets invalidated. You can manually compile it and it works fine again. However, USER_TRIGGERS still shows the old table name in the TRIGGER_BODY column, even though the column TABLE_NAME is correct.

Yong Huang
yong321_at_yahoo.com

In article <3A6CB390.D19B81CB_at_free.fr>,
  "M.RAMBELOSON" <MRambeloson_at_free.fr> wrote:
>
> For the first point OK.
>
> How can you tell us that using a table name in your code is not
 necessary,
> especially if it is about trigger.
> How would you code a trigger, fired on an insert, without naming your
 table
> in its body ?. I'm more than interested Mr Bakker.
>
> Regs
> Max.
>
> Sybrand Bakker wrote:
>
> > On Sat, 20 Jan 2001 00:23:14 GMT, "M.RAMBELOSON"
 <MRambeloson_at_free.fr>
> > wrote:
> >
> > >Hi all,
> > >
> > >Anyone can tell what happen to a trigger defined on a table that
 has
> > >been renamed ?. I met the case, it seems that the old table name
 remains
> > >in the trigger body, but the new one appers in the TABLE_NAME
 column of
> > >the DBA_TRIGGERS dictionary table ...
> > >
> > >Thanks in advance
> > >Max.
> > Which is quite logical. Do you a rename expect to 'edit' your pl/sql
> > code, which has been stored in a long. I don't think so.
> > Also, I'm not sure why you are using the table name in your code.
> > Usually that is not necessary.
> >
> > Regards,
> > Sybrand Bakker, Oracle DBA

Sent via Deja.com
http://www.deja.com/ Received on Mon Jan 22 2001 - 17:53:00 CST