Forum Stats

  • 3,828,128 Users
  • 2,260,865 Discussions
  • 7,897,487 Comments

Discussions

dynamic trigger for after insert on a table

Sekar_BLUE4EVER
Sekar_BLUE4EVER Member Posts: 324 Blue Ribbon
edited Jul 1, 2015 2:54AM in General Database Discussions

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

Tagged:

Answers

  • Hemant K Chitale
    Hemant K Chitale Member Posts: 15,759 Blue Diamond
    edited Jul 1, 2015 1:42AM

    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
    AndrewSayer Member Posts: 13,007 Gold Crown
    edited Jul 1, 2015 1:46AM

    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
    Sekar_BLUE4EVER Member Posts: 324 Blue Ribbon
    edited Jul 1, 2015 1:52AM

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

  • Hemant K Chitale
    Hemant K Chitale Member Posts: 15,759 Blue Diamond
    edited Jul 1, 2015 1:55AM

    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
    Geert Gruwez Member Posts: 1,011
    edited Jul 1, 2015 2:40AM

    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
    JohnWatson2 Member Posts: 4,452 Silver Crown
    edited Jul 1, 2015 2:50AM

    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
    edited Jul 1, 2015 2:54AM

    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

This discussion has been closed.