Sql-server – On DB2, who updated a record and what did the record look like before the update

db2iseriessql-server-2008transaction

(SQL Server 2008 R2 Standard, database under full recovery)

I have a table with fields id, firstname, lastname. A statement is executed:

insert into dbo.sometable (id, firstname, lastname) values (1, 'John', 'Smith')

After a few hours another statement is executed:

update dbo.sometable set firstname='Matt' where id=1

Is there a method or a tool that would allow us to see the history of a record (that a record was inserted on X date by Y person and these were the values inserted, and that same record was updated on A date by B person and these were the new values after the update)?

Users familiar with DB2 for i (ie, DB2 on the iSeries) might know of a TAA toolset command called CRTDBFJRN that can dump all transactions on a given table into another table giving a clear picture of the insert, the update and the field that was updated.

SQL Server Enterprise has CDC but we have a Standard edition; I could use triggers to track changed data by storing in an audit table. The use of triggers will require two more objects – a trigger and an audit table, and triggers would be fired on each change. It would be nice if a tool could replay transactions on demand from a transaction file.

What tools do you use or tasks do you perform when an auditor asks for a history of a financial record (inserted and updated on a single day, for example) such as: Who changed this record, what was changed, when was this record originally entered, what was the data when it was original entered etc?

Any help will be appreciated. Thank you.

Best Answer

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

enter image description here

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

enter image description here

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

enter image description here

Related Solutions

Sql-server – Why TRY CATCH does not suppress exception in trigger

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;

Mysql – How to UPDATE just one record in DB2

Actually, on the link you have, it mentions that you can update only one record, you just have to use cursors to do so. It then points you to the DECLARE CURSOR page.

So first, you would have to declare a cursor from your table with the select statement that would give you what you want.

DECLARE C1 CURSOR
FOR (SELECT * FROM MYTABLE WHERE...);

Move to the value of the record in the cursor that you want.

Then run you update statement against the cursor using the link you mention

UPDATE MYTABLE 
SET MYCOLUMN = ?
WHERE CURRENT OF C1;

I believe you need to issue a COMMIT (which closes the cursor unless you specify otherwise in the DECLARE CURSOR clause).

Check out the documentation in the link you have and in the one I have. You may end up having to use a stored procedure to run this.

Best Answer

Related Solutions

Sql-server – Why TRY CATCH does not suppress exception in trigger

Mysql – How to UPDATE just one record in DB2

Related Question