How to create audit trail for a table using Triggers. I am using sybase 15.2 ASE

Question

Iwant to post all changes of records of table copied into a separate table

CREATE TABLE dbo.mytable(
    field1 numeric(13,2) NOT NULL,
    field2 char(4)       NOT NULL,
    field3 char(4)       NOT NULL,
    field4 varchar(30)   NOT NULL,
    field5         datetime      NULL,
    field6 datetime      NULL)

This is my table.

I want to create a separate table, which will record, all changes, as and when changes to the above table..

 CREATE TABLE dbo.mytable_audit(
            field1 numeric(13,2) NOT NULL,
            field2 char(4)       NOT NULL,
            field3 char(4)       NOT NULL,
            field4 varchar(30)   NOT NULL,
            field5         datetime      NULL,
            field6 datetime      NULL,
    idactivity numaric),
    iddatestamp datetime)

I want to capture changes of first table into 2nd table (who did insert/update/delete on this table with time stamp).

score 0 · Accepted Answer · answered Aug 13 '13 at 13:51

There are Insert, Update, Delete and Instead of Triggers. You will want to create an Insert, Update and Delete triggers for your purpose.

Here's a short example:

create trigger mytable_del on mytable
for delete

as

declare @stuff

insert something into mytable_audit
where something and something else

end
go

Here's the documentation. If you have problems with specific parts of your triggers, then you can post those specific code questions here, but it's unlikely that someone here will write your triggers for you.

How to create audit trail for a table using Triggers. I am using sybase 15.2 ASE

1 Answers1