Log file

[dmarnerou]

Hi,
I created a database using SQL Server which I update through my delphi program. I want to know how I can write every change that occurs on the database (eg. when a record is inserted, or deleted) in a log file.
Please answer asap.

Thanks,
Demetra

 

[earlrainer]

what you can do is make a new table in your database.
and then you can write a trigger for all the tables (on insert or update) that will put the data into this new table. So whenever there is any database change , then the data will automatically go to this new table

 

[dmarnerou]

can you provide me with some source code pls?

 

[LucieLastic]

hi, for this stuff you'd be better looking at the SQLServer forum.

Here's an example of a trigger which writes to an audit table for an UPDATE. I was given a *lot* of help from someone on the SQL forum to write this so if you have any technical problems with it, you will probably have to go to the forum.

You will have to do seperate triggers for INSERT and DELETEs.

CREATE TRIGGER [TR_UPDATEmyTable] ON [myTable]
FOR UPDATE
AS
DECLARE @cvErrorMessage varchar(255), @BegineTransCount int

SELECT @BeginTransCount = @@TRANCOUNT
--put business rules before transaction

BEGIN TRANSACTION

--fill audit table only on name changes and what the record used to look like

INSERT myTable_AUDIT (field,
...all your fields...,
field )
SELECT d.modified<idfield>,
d. ...etc...

FROM deleted d
JOIN inserted i on i.idfield ON i.<idfield> = d.<idfield>
WHERE IsNull (i.modified_id, &quot;&quot <> isNull(d.modified_id, &quot;&quot

IF @@Error <> 0
BEGIN
SELECT @vcErrorMessage = &quot;Error: unable to write UPDATE audit record for <myTable>.&quot;
GOTO Error
END

error:

IF @vcErrorMessage IS NULL
BEGIN
IF @@TRANCOUNT > @BeginTransCount COMMIT TRANSACTION
END
ELSE
BEGIN
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
RAISEERROR ( @vcErrorMessage, 16, 1)
END