[dba-SQLServer] SQL Server 2005 LDF Viewer

Please ignore last email from me asking where Triggers are stored, was
having blonde moment...

Thanks again for everyone's help.

Paul

2008/9/15 Paul Hartland <paul.hartland at googlemail.com>

>  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
>



-- 
Paul Hartland
paul.hartland at googlemail.com