Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2009
    Posts
    4

    Unanswered: Need a trigger for this scenerio

    Hi,

    Consider there are two databases DB1 and DB1_Audit.
    . DB1 is the main database which contains all the tables required for the project say 100 tables.
    DB1_Audit is the audit database which contains selective tables say 30 tables.

    In the DB1_Audit database, the tables which contains the same structures as in DB1 in which table name is suffixed with
    _backup and two extra columns such as DMLOPERATION AND OCCURENCE_TIME at last.

    Ex:
    DB1:

    t1 table which has a,b,c are the column names.

    DB1_Audit

    t1_backup table which has a,b,c,DMLOPERATION and OCCURENCE_TIME columns.

    Functionality:

    1.
    When a record is inserted in t1 table, simultaneously record has to be inserted
    in DB1_audit.t1_backup table with the data as in t1 and
    DMLOPERATION ='Insert' and OCCURENCE_TIME = system date with time ( say sysdate in oracle).

    2.
    When a record is deleted from t1 table,
    simultaneously record has to be inserted
    in DB1_audit.t1_backup table with the data as in t1 and
    DMLOPERATION ='Delete' and OCCURENCE_TIME = system date with time .

    3.

    When a record is updated from t1 table,

    a)
    simultaneously record has to be inserted
    in DB1_audit.t1_backup table with the old data as in t1 and
    DMLOPERATION ='UpdateBefore' and OCCURENCE_TIME = system date with time .

    b)
    Record has to be inserted
    in DB1_audit.t1_backup table with the modified data as in t1 and
    DMLOPERATION ='UpdateAfter' and OCCURENCE_TIME = system date with time .


    Note:

    For all the DML operation in t1 table, insert has to be happened in Audit db.
    For insert and delete in t1 table- one record has to be inserted but for update two
    records with old data and new data has to be recorded.


    I need to write a trigger for the above functionality for t1 table in DB1 Db. Is it possible to write such a trigger ? If so kindly give me some guidance over it so that I can start.

  2. #2
    Join Date
    Apr 2008
    Location
    Along the shores of Lake Michigan
    Posts
    242
    My bad, I'm in the wrong forum.....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •