March 7, 2010 at 7:36 pm
select transaction_id from sys.dm_tran_current_transaction;
March 7, 2010 at 9:53 pm
You should definitely not use sp_getbindtoken for new developments.
Books Online: sp_getbindtoken
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
There are much better ways to perform auditing than outlined in your request, but given the exact scenario described, and the specific requirements, the following code demonstrates an approach using triggers. (The advantage being that no changes to existing code are required).
-- For demonstration purposes
USE tempdb;
GO
-- Drop the test tables if they exist from a previous run
DROP TABLE
dbo.UserActionAudit,
dbo.UserAction,
dbo.Client;
GO
-- ==============
-- TABLE CREATION
-- ==============
-- Client table
CREATE TABLE dbo.Client
(
client_id INTEGER PRIMARY KEY,
data VARCHAR(50) NOT NULL
);
-- User Action table
CREATE TABLE dbo.UserAction
(
user_action_id INTEGER IDENTITY PRIMARY KEY,
user_identifier INTEGER NOT NULL,
utc_date_time DATETIME NOT NULL DEFAULT (GETUTCDATE()),
action_type INTEGER NOT NULL,
client_id INTEGER NOT NULL
FOREIGN KEY REFERENCES dbo.Client
);
-- Audit table
CREATE TABLE dbo.UserActionAudit
(
audit_id INTEGER IDENTITY PRIMARY KEY,
transaction_id BIGINT NOT NULL,
user_action_id INTEGER NOT NULL
FOREIGN KEY REFERENCES dbo.UserAction,
client_id INTEGER NOT NULL
FOREIGN KEY REFERENCES dbo.Client,
is_complete BIT NOT NULL DEFAULT (0)
);
GO
-- ========
-- TRIGGERS
-- ========
-- AFTER INSERT trigger on User Action to create an audit record
-- tied to the current transaction id
CREATE TRIGGER [dbo.UserAction AI]
ON dbo.UserAction
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
SET ROWCOUNT 0;
IF NOT EXISTS (SELECT * FROM inserted) RETURN;
INSERT dbo.UserActionAudit
(
transaction_id,
user_action_id,
client_id
)
SELECT CT.transaction_id,
INS.user_action_id,
INS.client_id
FROM inserted INS
CROSS
JOIN sys.dm_tran_current_transaction CT;
END;
GO
-- AFTER UPDTE trigger on Client to update the audit record
-- Audit records are matched on the transaction id
CREATE TRIGGER [dbo.Client AU]
ON dbo.Client
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
SET ROWCOUNT 0;
IF NOT EXISTS (SELECT * FROM inserted) RETURN;
UPDATE UAA
SET client_id = INS.client_id,
is_complete = 1
FROM sys.dm_tran_current_transaction CT
JOIN dbo.UserActionAudit UAA
ON UAA.transaction_id = CT.transaction_id
JOIN inserted INS
ON INS.client_id = UAA.client_id;
END;
GO
-- =====
-- TESTS
-- =====
-- Five sample clients
INSERT dbo.Client
(client_id, data)
SELECT 1, 'Client 1' UNION ALL
SELECT 2, 'Client 2' UNION ALL
SELECT 3, 'Client 3' UNION ALL
SELECT 4, 'Client 4' UNION ALL
SELECT 5, 'Client 5';
-- Perform a transaction of the type we want to audit
BEGIN TRANSACTION;
-- Record the user action
INSERT dbo.UserAction
(user_identifier, action_type, client_id)
SELECT 12345, 3, 1 UNION ALL
SELECT 12345, 3, 2 UNION ALL
SELECT 12345, 3, 3;
-- Show the state of the audit table
SELECT UAA.audit_id,
UAA.transaction_id,
UAA.user_action_id,
UAA.client_id,
UAA.is_complete
FROM dbo.UserActionAudit UAA
ORDER BY
UAA.audit_id ASC;
-- Update the client table
UPDATE dbo.Client
SET data = 'Updated ' + data
WHERE client_id IN (1, 2, 3);
COMMIT TRANSACTION;
-- Show the state of the audit table
-- Notice is_complete = 1
SELECT UAA.audit_id,
UAA.transaction_id,
UAA.user_action_id,
UAA.client_id,
UAA.is_complete
FROM dbo.UserActionAudit UAA
ORDER BY
UAA.audit_id ASC;
-- Show the Client data
SELECT C.client_id,
C.data
FROM dbo.Client C
ORDER BY
C.client_id ASC;
-- Show the User Action data
SELECT UA.user_action_id,
UA.user_identifier,
UA.utc_date_time,
UA.action_type,
UA.client_id
FROM dbo.UserAction UA
ORDER BY
UA.user_action_id ASC;
GO
-- =======
-- TIDY UP
-- =======
DROP TABLE
dbo.UserActionAudit,
dbo.UserAction,
dbo.Client;
GO
Paul
edit: To add quote from Books Online
March 7, 2010 at 10:04 pm
I'm concerned about two issues regarding the use of dm_tran_current_transaction. First, that the access rights required to access this table may be greater than what is typically allowed. Second, the the BIGINT is not sufficiently large to be unique across the life of the database.
March 7, 2010 at 10:16 pm
Paul Rony
...the access rights required to access this table may be greater than what is typically allowed.
This is what the EXECUTE AS clause of CREATE TRIGGER is for.
Paul Rony
...the BIGINT is not sufficiently large to be unique across the life of the database.
At 100,000 transactions per second, the range of a BIGINT is sufficient for almost three million years.
Paul
March 7, 2010 at 10:20 pm
Will this approach work on SQL Azure?
March 7, 2010 at 10:45 pm
Paul Rony (3/7/2010)
Will this approach work on SQL Azure?
No idea. My posted example was to meet the requirements set by hjo-483301. As I said before, there are better ways.
March 22, 2011 at 6:56 am
SQLkiwi (3/7/2010)
As I said before, there are better ways.
Could you please provide some references to the better ways or perhaps explain briefly.
Much appreciated...
February 6, 2014 at 12:24 pm
Perhaps use sys.dm_tran_current_transaction.
declare @@flag int
select @@flag = 11
--While (@@flag > 0)
--begin
-- set @@flag -= 1
-- begin tran
-- select @@flag, *
-- from sys.dm_tran_current_transaction
-- rollback
--end
begin tran
While (@@flag > 0)
begin
begin tran
set @@flag -= 1
select @@flag, *
from sys.dm_tran_current_transaction
commit
end
rollback
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply