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.

My bad, I'm in the wrong forum.....