Journal table for a database

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> Is this possible by way of a common trigger.
NO.
Another option is running a profiler trace to log these changes.
But that has a performance impact.
--
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"Senthilkumar" <kesk32@.yahoo.co.in> wrote in message
news:ewqweKAGGHA.1124@.TK2MSFTNGP10.phx.gbl...
> 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
>|||hi Senthilkumar,
What about the creation of fields which store domain user or updating time
in every row
"Senthilkumar" wrote:
> 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
>
>|||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
"Senthilkumar" <kesk32@.yahoo.co.in> wrote in message
news:ewqweKAGGHA.1124@.TK2MSFTNGP10.phx.gbl...
> 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
>