I've tested two tools that can provide info about transactions including when, who, using which computer and application
ApexSQL Audit creates auditing triggers for you, so if you're not up to coding and creating triggers for each of your tables, this is an option
It has 2 built in reports, and the good thing about it is that it saves all captured transactions into 2 tables, so you can run queries against them and extract any info you need
Unfortunately, it can't replay the transactions
Another one is ApexSQL Log. It reads transaction logs and trn backups, so the database has to be in Full recovery model. Besides the data transactions, it also tracks schema changes (create table, alter function, etc) and it can replay transactions
The transaction is doomed with pretty much any exception and must be rolled back.
From "Using TRY...CATCH in Transact-SQL" on MSDN
Inside a TRY…CATCH construct, transactions can enter a state in which the transaction remains open but cannot be committed. The transaction cannot perform any action that would generate a write to the transaction log, such as modifying data or trying to roll back to a savepoint. However, in this state, the locks acquired by the transaction are maintained, and the connection is also kept open. The effects of the transaction are not reversed until a ROLLBACK statement is issued, or until the batch ends and the transaction is automatically rolled back by the Database Engine. If no error message was sent when the transaction entered an uncommittable state, when the batch finishes, an error message will be sent to the client application that indicates an uncommittable transaction was detected and rolled back.
A transaction enters an uncommittable state inside a TRY block when an error occurs that would otherwise have ended the transaction. For example, most errors from a data definition language (DDL) statement (such as CREATE TABLE), or most errors that occur when SET XACT_ABORT is set to ON, terminate the transaction outside a TRY block but make a transaction uncommittable inside a TRY block.
This example shows why. XACT_STATE() doesn't register an implied transaction (eg no explicit BEGIN TRAN)
CREATE TABLE TrgTest (gbn int NOT NULL);
GO
CREATE TRIGGER TRG_TrgTEst_I ON TrgTest AFTER INSERT
AS
BEGIN TRY
SELECT '1', @@TRANCOUNT, XACT_STATE();
RAISERROR('Test error', 16, 2);
END TRY
BEGIN CATCH
SELECT '2', @@TRANCOUNT, XACT_STATE();
END CATCH
GO
BEGIN TRANSACTION
INSERT TrgTest VALUES (1)
SELECT 'will not run'
GO
SELECT 'next batch'
GO
BEGIN TRY
BEGIN TRANSACTION
SELECT 'a', @@TRANCOUNT, XACT_STATE();
INSERT TrgTest VALUES (1)
SELECT 'b', @@TRANCOUNT, XACT_STATE();
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE()
SELECT 'c', @@TRANCOUNT, XACT_STATE();
END CATCH
GO
DROP TABLE TrgTest;
Best Answer
As Martin Smith said in the comments, there is no Date/Time based trigger, therefore you have two options.