Search code examples
sybasesap-aseaudit-trail

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


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).


Solution

  • 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.