Are Triggers Really that Bad or ....???

From: Norman Dunbar <Norman.Dunbar_at_lfs.co.uk>
Date: Fri, 16 Aug 2002 08:55:27 +0100
Message-ID: <E2F6A70FE45242488C865C3BC1245DA70283B919@lnewton.leeds.lfs.co.uk>

Morning Ronnie,

set lines 1000
set trimspool on

That way you'll get up to 1000 chars per line before it wraps, but if it is less than 1000 wide, the trailing spaces will be removed - you end up with variable length lines.

Just one thing, from 817, setting the linesize also screws up a DESC statement in SQLPlus - it makes the 'name' column very wide indeed :o(

Connected to:
Personal Oracle9i Release 9.2.0.1.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production

SQL> desc dept

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------
 DEPTNO                                    NOT NULL NUMBER(2)
 DNAME                                              VARCHAR2(14)
 LOC                                                VARCHAR2(13)

SQL> set lines 200
SQL> desc dept
Name

 DEPTNO                                                                 
 DNAME                                                                  
 LOC

SQL> By the way, at some point in the future you might need to edit the trigger you generated, so why not create it nicely formatted now ?

select 'Create or replace Trigger '||table_name||'_Trigger'||chr(10)||
' BEFORE INSERT OR UPDATE ON '||table_name||chr(10)||
' for each row'||chr(10)||
' BEGIN'||chr(10)||
' IF inserting THEN'||chr(10)||
' :NEW.created_date:=sysdate;'||chr(10)||
' :NEW.created_by:=user;'||chr(10)||
' ELSE'||chr(10)||
' :NEW.updated_date:=sysdate;'||chr(10)||
' :NEW.updated_by:=user;'||chr(10)||
' END IF;'||chr(10)||
'END;'||chr(10)||
'/'

from dba_tab_columns where column_name='CREATED_DATE'

I'd also run the above in the user which owns the tables you want to put the triggers on and select from user_tab_columns instead of dba_tab_columns, because when you run the generated script you'll be creating triggers on tables which are not in SYSTEM (which I presume you are logged in to) and also, if there are different users in the database with the same name for a table in each with a column named
'CREATED_DATE' then you'll have even more problems if you select from
dba_tab_columns.

HTH Regards,
Norman.

Norman Dunbar
Database/Unix administrator
Lynx Financial Systems Ltd.
mailto:Norman.Dunbar_at_LFS.co.uk

Tel: 0113 289 6265
Fax: 0113 289 3146
URL: http://www.Lynx-FS.com
-------------------------------------

-----Original Message-----
From: Ronnie Yours [mailto:ronnie_yours_at_yahoo.com] Posted At: Thursday, August 15, 2002 6:26 PM Posted To: server
Conversation: Are Triggers Really that Bad or ....??? Subject: 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.

<SNIP> Received on Fri Aug 16 2002 - 02:55:27 CDT