Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: DDL for triggers.

Re: DDL for triggers.

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Thu, 26 Oct 2006 21:26:02 +0200
Message-ID: <ehr249$t7g$00$1@news.t-online.com>


joel garry schrieb:
> I would like a script to grab this. It seems to be a little trickier
> than one would expect.
>
> So far the closest for my needs is the simple orafaq mktrig, but it has
> a problem with very long trigger bodies - I have some that approach
> 12000 characters, and I haven't found a combination of sqlplus settings
> that doesn't break a variable name in the middle. If someone can fix
> that, please say something!
>
> One from metalink seems to be confused about WHEN clauses. Others on
> this group seem limited by age.
>
> I have several hundred triggers that are mysteriously generated by a
> 4GL, and the newer versions of the product seem to have lost a previous
> ability to handle all this when importing and changing schema names.
> The vendor is all "don't change schema names" (as if I want schemata
> named "*prod" in my test environment! Not to mention the need of
> having more than one schema per database, since what I'm really working
> on is splitting out information.), and oracle is all "recreate the
> triggers after import." imp manages to come up with create trigger
> test.triggername ... on prod.tablename, oh, duh, that doesn't exist on
> the test db (and what if it did?? - as it will when this is no longer
> in test)!
>
> I've managed to get through this cycle by manually fixing the broken
> creates, either through fixing what mktrig almost did, or saving the
> ddl on a pc through EM, editing it in notepad for shorter lines and
> ftping, or using the show with imp (ugh! that was the worst!). But I
> really would like a script that:
>
> Gives all the triggers for a schema (nothing too obscure featurewise,
> just some triggers are big).
> Keeps them in a format I can edit with vi. (I could perhaps live with
> sed if I can't)
> Works without manual intervention.
> On hp-ux 9206 and above.
> Using native tools.
>
> I don't see anything about triggers in dbms_metadata... am I missing
> something?
>
> I may yet go back to strings on the dump... but that's slow because
> it's a big dump.
>
> TIA!
>
> jg

I don't have 9i actually to test, but i doubt, it would behave differently. Here is for 10gR2

SQL> select * from v$version

   2 /

BANNER



Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Prod PL/SQL Release 10.2.0.2.0 - Production
CORE 10.2.0.2.0 Production
TNS for Linux: Version 10.2.0.2.0 - Production NLSRTL Version 10.2.0.2.0 - Production

SQL> set long 1000000 pages 0
SQL> select dbms_metadata.get_ddl('TRIGGER','FILES_TRG') from dual;

   CREATE OR REPLACE TRIGGER "MAXIM4711"."FILES_TRG" BEFORE INSERT OR DELETE ON files
FOR EACH ROW
BEGIN
   IF Inserting THEN
     SELECT Files_Seq.NEXTVAL INTO :NEW.Id FROM Dual;    END IF;
   IF Deleting THEN

     INSERT INTO Files_History
       (Id,
        Parent_Id,
        File_Name,
        File_Description,
        Content,
        Clob_Content,
        Mime_Type,
        Created,
        Createdby)
     VALUES
       (:OLD.Id,
        :OLD.Parent_Id,
        :OLD.File_Name,
        :OLD.File_Description,
        :OLD.Content,
        :OLD.Clob_Content,
        :OLD.Mime_Type,
        :OLD.Created,
        :OLD.Createdby);

   END IF;
END; ALTER TRIGGER "MAXIM4711"."FILES_TRG" ENABLE Best regards

Maxim Received on Thu Oct 26 2006 - 14:26:02 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US