Oracle-L: RE: Script to grab trigger source with conditions?

From: Chelur, Jayadas {PBSG} <jayadas.chelur_at_pepsi.com>
Date: Tue, 06 May 2003 07:22:09 -0800
Message-ID: <F001.00591106.20030506072209@fatcity.com>

>Rachael and Jayadas both forwarded me scripts.  I'm
>currently struggling with both of them, still not
>getting the entire trigger.

Hi Barabra,

Here is a script which I am using to get the scripts of all triggers in the schema. When you run the script it will create a script ALLTRIGS.SQL. You can run that that will create individual files for each trigger in the schema with the format <trigger_name>_trg.sql. We use it take backup copies of triggers. ( for updating copies in Visual Source Safe ).

HTH ... Regards,
Jayadas

SET ECHO OFF
SET VERIFY OFF
SET FEEDBACK OFF
SET TERMOUT OFF
SET HEADING OFF
SET PAGESIZE 0
SET LONG 8000
SET LINESIZE 300
SET TRIMSPOOL ON
SPOOL ALLTRIGS.SQL SELECT
'SET ECHO OFF' ||CHR(10)||
'SET VERIFY OFF' ||CHR(10)||
'SET FEEDBACK OFF' ||CHR(10)||
'SET TERMOUT OFF' ||CHR(10)||
'SET HEADING OFF' ||CHR(10)||
'SET PAGESIZE 0' ||CHR(10)||
'SET LINESIZE 300' ||CHR(10)||
'SET LONG 8000' ||CHR(10)||
'SPOOL '||trigger_name||'_trg.sql'||CHR(10)||
'SELECT '||''''||'CREATE OR REPLACE TRIGGER '||''''||'||' ||

        'trigger_name' || '||' || '''' || ' ' || '''' || '||' ||CHR(10)||
        'DECODE(SUBSTR(trigger_type,1,1),'||CHR(10)||
               ''''||'A'||''''||','||''''||'AFTER '||''''||','||CHR(10)||
               ''''||'B'||''''||','||''''||'BEFORE '||''''||','||CHR(10)||
               ''''||'I'||''''||','||''''||'INSTEAD OF '||''''||')
||'||CHR(10)||
        'triggering_event||'||CHR(10)||
        ''''||' ON '||''''||'||'||'table_owner||'||''''||'.'||''''||'||'||
        'table_name||'||CHR(10)||
	' DECODE(referencing_names,'||''''||'REFERENCING NEW AS NEW OLD AS
OLD'||''''||
	',NULL,referencing_names)||'||CHR(10)||
        ' DECODE(INSTR(trigger_type,'||''''||'EACH ROW'||''''||'),0,NULL,'||
          ''''||' FOR EACH ROW '||''''||')||'||CHR(10)||
	'
DECODE(when_clause,NULL,NULL,'||''''||'('||''''||'||'||'when_clause||'||
	  ''''||')'||''''||'),'||CHR(10)||
        ' trigger_body'||CHR(10)||

' FROM USER_TRIGGERS '||CHR(10)||
' WHERE trigger_name = '||''''||trigger_name||''''||';'||CHR(10)||
'PROMPT / ' ||CHR(10)||
'SPOOL OFF' ||CHR(10)||
'SET VERIFY ON' ||CHR(10)||
'SET FEEDBACK ON' ||CHR(10)||
'SET TERMOUT ON' ||CHR(10)||
'SET HEADING ON'

FROM USER_TRIGGERS; SPOOL OFF
SET HEADING ON
SET VERIFY ON
SET FEEDBACK ON
SET ECHO ON
SET TERMOUT ON

-----Original Message-----
Sent: Monday, May 05, 2003 4:32 PM
To: Multiple recipients of list ORACLE-L

Yes, Igor, you're correct! It is a when clause.

Rachael and Jayadas both forwarded me scripts. I'm currently struggling with both of them, still not getting the entire trigger.

I think Kirti's correct about setting long, since I'm loosing pieces.

Thanks for your responses.

Barb

Igor Neyman <ineyman_at_perceptron.com> wrote: > Barbara, > > Did you mean "when" clause (not "where" clause)? > There is "when_clause" column in > all/dba/user_triggers. > So, you could just add it to the script, you have. > > Igor Neyman, OCP DBA > ineyman_at_perceptron.com > > > > ----- Original Message ----- > To: "Multiple recipients of list ORACLE-L" > <ORACLE-L_at_fatcity.com> > Sent: Monday, May 05, 2003 12:32 PM > > > > Hi, all. > > Does anyone have a script they're willing to share > > that will capture the trigger source including a > > "where" clause? > > > > I got the text below from asktom. It grabs > everything > > except the where. Didn't find anything in our > > archives or google. > > > > TIA for any help! > > Barb > > Oracle 8.1.7.4 > > Solaris 2.6 > > > > select > > 'create or replace trigger "' || > > trigger_name || '"' || chr(10)|| > > decode( substr( trigger_type, 1, 1 ), > > 'A', 'AFTER', 'B', 'BEFORE', 'I', > 'INSTEAD > > OF' ) || > > chr(10) || > > triggering_event || chr(10) || > > 'ON "' || table_owner || '"."' || > > table_name || '"' || chr(10) || > > decode( instr( trigger_type, 'EACH ROW' ), 0, > null, > > 'FOR EACH ROW' ) || chr(10) , > > trigger_body > > from user_triggers > > where trigger_name = upper('&1') > > > > > > __________________________________ > > Do you Yahoo!? > > The New Yahoo! Search - Faster. Easier. Bingo. > > http://search.yahoo.com > > -- > > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > > -- > > Author: Barbara Baker > > INET: barbarabbaker_at_yahoo.com > > > > Fat City Network Services -- 858-538-5051 > http://www.fatcity.com > > San Diego, California -- Mailing list and > web hosting services > > >
> > To REMOVE yourself from this mailing list, send an > E-Mail message > > to: ListGuru_at_fatcity.com (note EXACT spelling of > 'ListGuru') and in > > the message BODY, include a line containing: UNSUB > ORACLE-L > > (or the name of mailing list you want to be > removed from). You may > > also send the HELP command for other information > (like subscribing). > > > > > > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > -- > Author: Igor Neyman > INET: ineyman_at_perceptron.com > > Fat City Network Services -- 858-538-5051 > http://www.fatcity.com > San Diego, California -- Mailing list and web > hosting services >
> To REMOVE yourself from this mailing list, send an > E-Mail message > to: ListGuru_at_fatcity.com (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing). >

Do you Yahoo!?
The New Yahoo! Search - Faster. Easier. Bingo. http://search.yahoo.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Barbara Baker
  INET: barbarabbaker_at_yahoo.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Chelur, Jayadas {PBSG}
  INET: jayadas.chelur_at_pepsi.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Tue May 06 2003 - 10:22:09 CDT