Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: DDL for triggers.
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
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);
Maxim Received on Thu Oct 26 2006 - 14:26:02 CDT