Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Are Triggers Really that Bad or ....???
Hi,
Thanks a lot chr(10) works great.
But now how do i set the linesize.
If I leave it default it goes to the next line after the 80th column and
then it does not compile properly.
If I set it to 1000 then its too big.
Is there a way to handle this.
This is my statement
select 'Create or replace Trigger '||table_name||'_Trigger BEFORE INSERT OR
UPDATE ON '||
table_name||' for each row BEGIN IF inserting THEN
:NEW.created_date:=sysdate; :NEW.created_by:=user; END IF; If Updating
then :NEW.updated_date:=sysdate; :NEW.updated_by:=user; end if;
END;'||chr(10)||'/'
from dba_tab_columns where column_name='CREATED_DATE'
Thanks
Ronnie
"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message
news:ligllu0luf0rc7niorvnv7p3a5pm52b6fd_at_4ax.com...
> On Wed, 14 Aug 2002 16:16:57 -0400, "Ronnie Yours"
> <ronnie_yours_at_yahoo.com> wrote:
>
> >Hi,
> >
> >I tried playing around with the idea of reducing the amount of work
needed
> >to automate the process of creating triggers for all the tables and this
is
> >what i came up with
> >
> >select 'Create or replace Trigger '||table_name||'_Trigger BEFORE INSERT
OR
> >UPDATE ON '||
> >table_name||' for each row BEGIN IF inserting THEN
> >:NEW.created_date:=sysdate; :NEW.created_by:=user; END IF; If Updating
> >then :NEW.updated_date:=sysdate; :NEW.updated_by:=user; end if; END; /'
> >from dba_tab_columns where column_name='CREATED_DATE'
> >
> >I spool the above query into a file and execute it.
> >
> >It would work fine , but the only problem is the / at the end of the
> >procedure it creates. The / should appear on the next line and I am not
able
> >to do so. Is there a way I can force a carriage return before the /.
> >
> >If i manually edit the output of the above query so that the / appears
on
> >the next line of each result set, it works great.
> >
> >Please suggest
> >
> >Thanks
> >Ronnie
> >
> simply use 'END;'||chr(10)||'/'
> That should do it
>
> Hth
>
>
> Sybrand Bakker, Senior Oracle DBA
>
> To reply remove -verwijderdit from my e-mail address
Received on Thu Aug 15 2002 - 12:26:00 CDT