[dba-SQLServer] SQL Server 2005 LDF Viewer

Asger,

I have created a FOR DELETE trigger, and fingers crossed no more records
have disappeared since, however how do I view the trigger that I have
created in SQL Server 2005 ?

Thanks for your help.

Paul

2008/9/12 Asger Blond <ab-mi at post3.tele.dk>

> 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
>
>
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>


-- 
Paul Hartland
paul.hartland at googlemail.com