dynamic trigger for after insert on a table

Sekar_BLUE4EVER

hi,

     i need to create a trigger to insert into a table after a row is inserted into a table.But the issue I am facing is that I am not able to refer the entire row using :new.* so in case the structute of the table changes I would have to recreate the trigger.Is there any way to make the trigger dynamic for eg: like reading the column_names from dba_tab_columns and then using it in the query?

While trying to use the abobe case I am not able to convert the multiple rows returned from dba-tab_colums into a single row with many columns.

i.e I want to make

col

col1

col2

into

col     col1    col2

3 rows into 3 columns how do I do this

Hemant K Chitale

It would make the trigger slow.

How frequently would you expect the table structure to change ?  Part of your schema maintenance instructions would be to include the trigger changes with the table changes.

Hemant K Chitale

AndrewSayer

What is the actual business requirement here? I cant think of any reason you'd want to do this other than keeping a logical backup of your tables, but that is not the way you would go about it.

Triggers are generally  not the solution to most problems.

Sekar_BLUE4EVER

The requiremnet is to replicat a few tables to a remote db which will include different schema tables.

Hemant K Chitale

Don't use a trigger to replicate data to a remote database.  Network outage or non-availability of the remote database will prevent transactions on the local database.

Use Materialized Views (available in Standard Edition as well) or Advanced Replication (available in Enterprise Edition) or Golden Gate (separately priced product).

Hemant K Chitale

Geert Gruwez

the statements inside the trigger are part of the actual transaction

if the remote site is unavailable, your statement won't work

if it's very remote you might have some slow issues

how *fast* do those changes have to get on the other site ?

JohnWatson2

These solutions all sound awfully complicated. A multitable insert through a database link might be faster, simpler, and more reliable,

http://docs.oracle.com/database/121/SQLRF/statements_9014.htm#i2125362

Unknown

PL/SQL has a construct called SUBTYPE.

SUBTYPES allow <tablename>%ROWTYPE syntax.

You need to set up a procedure with a parameter which is defined as a SUBTYPE and passes the entire row as a PL/SQL record.

You need to recompile this procedure (it will be a procedure in a package, one package per table) everytime the table is ALTERED.

It is unclear to me why you 'need' this, it seems to be a recipe for disaster.

Also I don't understand why it is still too difficult for you to specify your four digit Oracle version.

You have been here long enough to know this is mandatory.

---------------

Sybrand Bakker

Senior Oracle DBA