Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: I need a trigger to automatically extend the length of a column.
On Mon, 9 Mar 1998 14:55:20 -0500, "Michael MacDonald" <MichaelMacDonald_at_iname.com> wrote: Hi,
its impossible to issue ALTER-commands in triggers since these commands implicitely COMMIT, which is forbidden in triggers. Maybe you could use a pipe to another session to issue your modify there and waiting with your insert 'til you get the ok from the other session.
>I need a trigger that will automatically extend the length of a column in a
>table
>before data is inserted which would otherwise fail due to column size
>restriction.
>
>The basic logic would be similar to:
>
>create or replace trigger
>before
> insert or update
>on
> TABLENAME
>for each row
>when
> (length(new.COLUMNNAME) > columnwidth)
>begin
> update table
> TABLENAME
> modify
> (COLUMNNAME varchar2(length(new.COLUMNNAME)))
>end;
>
>Assuming that the column is of type varchar2. I am also uncertain as to how
>to determine the existing columnwidth for the when clause above.
>
>Any help would be greatly appreciated.
>
>
>-Michael MacDonald.
>
>
>
>
-- Regards Matthias Gresz :-) GreMa_at_T-online.deReceived on Tue Mar 10 1998 - 00:00:00 CST