#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Posts
    1
    Rep Power
    0

    Question How can i write a trigger which triggers when update/insert data in any of the table


    Hi,
    i am using oracle8i. i need to write a trigger which fire when any of the table in the schema has been modified(ie,insert/update).
    And i need to get the table name and the column name which has been modified.
    So that i can insert one record in another destination table which keep in track of the changes.
    the structure ofthe destination table is
    1. id
    2.old value
    3.new value
    4.sourse_table
    5.sourse_column
    ......i can write separate trigger for each table..and can update the destination table.By using :new.value and ld.value..
    But i am searching for a common TRIGGER ..Which can do the job..
    Please help me.....
    regards,
    nandakumar.
  2. #2
  3. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Location
    New Delhi
    Posts
    3
    Rep Power
    0

    Schema Trigger for drop table


    First create a log table as

    Create table log (a varchar2(500));

    Then write the following trigger in your desired schema


    CREATE OR REPLACE TRIGGER no_drop_trg
    BEFORE DROP ON UTDSPROD.SCHEMA
    DECLARE
    v_msg varchar2(500);
    BEGIN
    v_msg :='Table '||DICTIONARY_OBJ_NAME ||' is dropped by '||LOGIN_USER;
    insert into log values(v_msg);
    END;

IMN logo majestic logo threadwatch logo seochat tools logo