Hi,

I am using a SQL Server 2000 database with a VB.net program and uses
Integerated security. I want to log all the changes made to any table
data(whether addition, deletion, updation) to a single table by all the
users.

Is this possible by way of a common trigger. I dont want to use the log file
to know since i have to buy a seperate program to explore the log file. How
can achieve this.

Thanks for all the answers.

Senthilkumar

Another option is running a profiler trace to log these changes.
But that has a performance impact.

Show quoteHide quote

hi Senthilkumar,

What about the creation of fields which store domain user or updating time
in every row


Show quoteHide quote

You can however implement something generic as example below for all
your tables:

CREATE TRIGGER logChanges
ON [any_table]
FOR INSERT, UPDATE, DELETE
AS

DECLARE @trigType char(1), @rwCnt int

SET @rwCnt = @@ROWCOUNT

IF @rwCnt = 0 RETURN

IF(select count(*) from inserted) > 0
BEGIN
        SET @trigType = 'i'

        IF(select count(*) from deleted) > 0
        BEGIN
                SET @trigType = 'u'
        END

END
ELSE
BEGIN
        SET @trigType = 'd'
END

IF @trigType = 'i'
BEGIN
    INSERT INTO
aLogTableWithSameColumnsAsOrigTablePlusLogTypeAndLogDateColumn (val1,
val2, val3, LogType, LogDate)
    SELECT val1, val2, val3, 'i', GETDATE() from inserted
END

IF @trigType = 'u'
BEGIN
    INSERT INTO
aLogTableWithSameColumnsAsOrigTablePlusLogTypeAndLogDateColumn (val1,
val2, val3, LogType, LogDate)
    SELECT val1, val2, val3, 'ui', GETDATE() from inserted
    UNION
    SELECT val1, val2, val3, 'ud', GETDATE() from deleted
END

IF @trigType = 'd'
BEGIN
    INSERT INTO
aLogTableWithSameColumnsAsOrigTablePlusLogTypeAndLogDateColumn (val1,
val2, val3, LogType, LogDate)
    SELECT val1, val2, val3, 'd', GETDATE() from inserted
END

GO

Thanks for all those who replied.

I was creating a trigger for each table to store the values in a different
table. but then i found a tool ApexAudit which exactly does what i want. I
have used it now.

Once again thanks for all the answers.

Senthilkumar


Show quoteHide quote

Optimizing an IN clause
SQL 2k5 vs 2k - different results on same execution? ORDER BY prob
What is this called, and how to do it?
SQL Server vs the Competition
Query Problem