Export a trigger

[mariocq]

Hello:

First of all I am not a DBA so I excuse myself if this is an easy question. We want to export a trigger from one database to another database, how can I do this?(I only want the trigger not the data)

Regards

 

[taupirho]

Hi, the code for the trigger is contained in the dba_triggers table owned by system. Its simply a matter of selecting out the data from this table in order for you to reconstitue the actual trigger code and then re-apply this code to the new database.

Alternatively ( and it comes down to the same thing really) you could also do an export/import on this table.

 

[hoinz]

Hello,

I disagree with this:
... you could also do an export/import on this table.

Actually dba_triggers isn't a table, but a view, based on various system tables.

regards

 

[taupirho]

Agreed

 

[SantaMufasa]

...and to clarify for subsequent Tek-Tipsters that may refer to this thread for guidance on this topic:

1) Oracle (unfortunately) has no easy, built-in method for exporting specific TRIGGERS.

2) Export dump files can contain triggers, but they are an artifact of exporting the tables upon which the triggers reside.

3) If you wish to install a trigger only from one database environment to another database environment, then perhaps the simplest method is to follow taupirho's excellent advice -- to display the trigger's code via a SELECT against a *_TRIGGERS view from the database's data dictionary, then copy and paste the results into a text file, which you then "scrub", then execute in the target environment.

Let us know if you have additional questions on this topic.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]