Re: Transaction log and trigger

From: Richard G (rich.gaston_at_gte.net)
Date: 06/07/04 

Date: Sun, 6 Jun 2004 23:40:40 -0700

You would have to "recover" the database to re-create the trigger. Once you
recover the database you will not be able to apply subsequent transaction
log backups.

"csar" <anonymous@discussions.microsoft.com> wrote in message
news:975B4970-E4B5-4D2B-BA49-45FCDA5AE494@microsoft.com...
Hari
    Thanks for the response.
But say my database set to a full recovery model and recover the databse
till the time the trigger is in place.
and then apply transaction log which was backed up for the next hour(by this
time the trigger would have been deleted). once this is completed, i
manually recreate the trigger to the table and apply the subsequest
transaction log for the table. when the changes from the transaction log is
applied to the table will the trigger get triggered and populate the audit
table?

Thanks
Saravanan

     ----- Hari Prasad wrote: -----

     Hi,

     You can recover the database till May 01st 09:59 AM provided your
database
     is set to FULL recovery model and you have
     all the transaction log backups.

     Recreate the trigger and apply the changes on the table from the
transaction
     log after the trigger is deleted(from may 1st 10.01 am?

     Did you meant , after creating the trigger , if you apply the
transaction
     logs will the auditing be enabled for transactions after May 01 10:01
AM?

     This cant be done by just applying the transation log backups.
Transaction
     log backups only contains the logs of the transaction you had.
     As well as your database should be in NO Recovery mode/ readonly till
you
     restore all transaction log files. So you cant even create the
     trigger in between.

     Thanks
     Hari
     MCDBA

     "csar" <anonymous@discussions.microsoft.com> wrote in message
     news:CDF17E13-4BC0-4904-A349-4C9AD8B6C12B@microsoft.com...
> Hi
> I have a problem with one of a trigger getting deleted in
production
     and the audit for the table is not present for the past month(from may
1st
     10 am). Is it possible for me to restore the database from until the
time
     the trigger was active (till may 1st 9.59 am) and recreate the trigger
and
     apply the changes on the table from the transaction log after the
trigger is
     deleted(from may 1st 10.01 am?
>> Thanks
> csar

Relevant Pages

  • Re: huge log file when inserting data
    ... Nothing else is running on the database. ... No trigger. ... The insert is in a transaction and it is committed. ... >space requirements. ...
    (microsoft.public.sqlserver.server)
  • Re: SQL 7 vs. 2000 issue -trigger and nulls
    ... >We're having trouble with a trigger updating some tables. ... course be locked by the current transaction, ... locking data and updating rows when the COMMIT inside the trigger is ... I seriously hope that SQL Server 7.0 simply disregarded these two ...
    (microsoft.public.sqlserver.mseq)
  • Re: Advanced - C# SQL Trigger Question
    ... fails, the transaction fails, and you don't have to do anything. ... I'm using a C# object to commit new rows to a> database. ... In the database I have an INSERT Trigger watching values come> in. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Auditing changes made to table design (syscolumns table)
    ... With out using profiler the only way I know of is to get this info from the ... Keep in mind I know the database and the tables I would ... I attempted to write a trigger to log when a change was made to the ... > certain criteria against every transaction. ...
    (microsoft.public.sqlserver.security)
  • Re: Alternative to Transactional Replication
    ... Basically the insert is wrapped in a transaction when you fire a trigger. ... Looking for a SQL Server replication book? ... with various WAN links. ...
    (microsoft.public.sqlserver.replication)