Re: Trigger Script Generation

From: Michel Cadot <micadot_at_netcourrier.com>
Date: Tue, 11 Jan 2000 13:43:16 +0100
Message-ID: <85f8h5$1k1$1@news3.isdnet.net>

Here's an Oracle7 script:

Define Valid_ = 'N' -- 'Y' if you only want the valid objects Set echo OFF
Set embedded ON
Set feedback OFF
Set heading OFF
Set linesize 2000
Set long 32760
Set newpage 0
Set pagesize 0
Set recsep OFF
Set space 0
Set termout OFF
Set trimout ON
Set trimspool ON
BTitle OFF
TTitle OFF
Spool crtrigger.sql
Drop table mcsql
/
Create table mcsql

   (id number, subid number,
    codesql varchar2(2000) /* LONG impossible a cause de l'affichage */,     primary key (id, subid))
/
Declare

   /* Curseur des objets a recreer */
   Cursor ct is

      select table_owner, table_name, trigger_name, description, trigger_body
      from user_objects obj2, user_triggers trg, user_objects obj
      where obj2.status like decode ('&Valid_', 'Y', 'VALID', '%')
        and obj2.object_name = trg.trigger_name
        and trg.status like decode ('&Valid_', 'Y', '%ABLED', '%')
        and trg.table_owner = USER
        and trg.table_name = obj.object_name
        and obj.status like decode ('&Valid_', 'Y', 'VALID', '%')
        and obj.object_type = 'TABLE'
      order by table_owner, table_name, trigger_name;

   /* Curseur des objets invalides */
   Cursor ct2 is

      select table_owner, table_name, trigger_name,
             obj2.status||' - '||trg.status
      from user_objects obj2, user_triggers trg, user_objects obj
      where ( trg.status = 'ERROR' or obj2.status != 'VALID' )
        and obj2.object_name = trg.trigger_name
        and trg.table_owner = USER
        and trg.table_name = obj.object_name
        and obj.status = 'VALID'
        and obj.object_type = 'TABLE'
        and '&Valid_' = 'Y'
      order by table_owner, table_name, trigger_name;

   nomTrigger user_triggers.trigger_name%TYPE;    nomOwner user_triggers.table_owner%TYPE;    nomTable user_triggers.table_name%TYPE;    description varchar2(2000);
   source varchar2(32760);
   nomTrigger2 user_triggers.trigger_name%TYPE;    nomOwner2 user_triggers.table_owner%TYPE;    nomTable2 user_triggers.table_name%TYPE;    erreur varchar2(100);
   codeSql varchar2(32760);
   valeur varchar2(2000);
   id number := 0;
   subid number;
   i number;
   lg number;
   espace boolean;
Begin

   Open ct;
   Open ct2;
   Fetch ct into nomOwner, nomTable, nomTrigger, description, source;    Fetch ct2 into nomOwner2, nomTable2, nomTrigger2, erreur;    Loop

      Exit when ( ct%NOTFOUND and ct2%NOTFOUND );
      If ( ct%NOTFOUND
          or ( ct2%FOUND
              and nomOwner2||'#'||nomTable2||'#'||nomTrigger2
                  < nomOwner||'#'||nomTable||'#'||nomTrigger ) ) then
         valeur := '
-- '||nomTrigger2||' ('||nomOwner2||'.'||nomTable2||') -> '||erreur;
         Insert into mcsql values (id, 0, valeur);
         Fetch ct2 into nomOwner2, nomTable2, nomTrigger2, erreur;
      Else
         codeSql := '
Create or replace trigger '||description;
         codeSql := codeSql||substr(source,1,length(source)-1)||'/';
         subid := 0;
         i := 1;
         lg := 0;
         espace := False;
         While ( i <= length (codeSql) ) loop
            If ( (i+2000) <= length (codeSql) ) then
               lg := 2000;
               While ( substr(codeSql, i+lg, 1) != '
' and lg > 0 ) loop
                  lg := lg - 1;
               End loop;
               If ( lg = 0 ) then
                  lg := 2000;
                  While ( substr(codeSql, i+lg, 1) != ' ' and lg > 0 ) loop
                     lg := lg - 1;
                  End loop;
                  If ( lg = 0 ) then
                     lg := 2000;
                  Else
                     lg := lg - 1;
                     espace := True;
                  End if;
               Else
                  lg := lg - 1;
                  espace := True;
               End if;
            Else
               lg := length (codeSql) - i + 1;
            End if;
            If ( lg > 0 ) then
               valeur := substr(codeSql, i, lg);
               Insert into mcsql values (id, subid, valeur);
               subid := subid + 1;
               i := i + lg;
            End if;
            If ( espace ) then
               i := i + 1;
            End if;
         End loop;
         Fetch ct into nomOwner, nomTable, nomTrigger, description, source;
      End if;
      id := id + 1;

   End loop;
   Close ct;
   Close ct2;
   Commit;
End;
/
Select codeSql from mcsql order by id
/
Spool off

Sorry for french comments and variable names.

--
Have a nice day
Michel

Jim Katsos <jkatsos_at_oz.quests.com> a écrit dans le message : dexe4.19171$lv5.75531_at_news-server.bigpond.net.au...
> Does anyone have some pl/sql which generates trigger ddl from the
> dba_triggers view.

>

> --
> Jim Katsos
> Quest Software Pty Ltd
> http://www.quests.com
> tel +61 3 9811 8068
> mob 0411088578

>
> Received on Tue Jan 11 2000 - 06:43:16 CST