[dba-SQLServer] SQL Server 2005 LDF Viewer

For this I would create an audittable and a FOR DELETE TRIGGER.
Suppose your tblPersonnel has columns like PersonnelID, FirstName, and
LastName. You want to audit the record deleted plus the time of the event,
and the user performing the deletion:

CREATE TABLE AuditDelete 
(PersonnelID int, FirstName varchar(30), LastName varchar (30), Deletion
varchar(100) DEFAULT CONVERT(varchar(50), GETDATE()) + '. By: ' +
SUSER_SNAME())
GO

CREATE TRIGGER tr_tblPersonnel_Delete ON tblPersonnel FOR DELETE AS
INSERT INTO AuditTable (PersonnelID, FirstName, LastName) SELECT
PersonnelID, FirstName, LastName FROM deleted
GO

Using a delete-trigger your can also cancel the event issuing a rollback.
Suppose you want to prevent deletion of multiple rows in the personnel
table, you could create the trigger like this:

CREATE TRIGGER tr_tblPersonnel_Delete ON tblPersonnel FOR DELETE AS
IF (SELECT COUNT(*) FROM deleted) > 1
BEGIN
	RAISERROR ('Deletion of multiple records not permitted!',16,1)
	ROLLBACK TRANSACTION
END
ELSE
INSERT INTO AuditTable (PersonnelID, FirstName, LastName) SELECT
PersonnelID, FirstName, LastName FROM deleted
GO

Asger
-----Oprindelig meddelelse-----
Fra: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af Paul
Hartland
Sendt: 12. september 2008 09:26
Til: Access List; SQLServerList
Emne: [dba-SQLServer] SQL Server 2005 LDF Viewer

To all,

Over the last two days we have been losing personnel off our main database,
and have had to retrieve them from one of my many backups.  However I can't
seem to see why or where this is happening in my application (VB6)...Does
anybody know how to view the LDF files, so that I can see each transaction
as it takes place, or if there is something I can do when a record is
deleted from tblPersonnel, or if I can turn on some sort of monitoring
system ?

Thanks in advance for any help on this.

-- 
Paul Hartland
paul.hartland at googlemail.com
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com