在SQL Server中触发-获取为审核表完成的事务类型 [英] Trigger in SQL Server - Get the type of Transaction done for Audit Table
问题描述
我正在编写触发器以将记录插入我的 Audit 表中。
I am writing trigger for inserting records into my Audit table.
每当数据发生更改时在我的目标表中，触发器将更新审计表的旧值，新值
Whenever there is a change in data in my target table, the trigger will update the old value, new value to the audit table
此外，还有名为 Transaction 和 Transaction_Status
Transaction 列定义了交易类型。可以是 INSERT ， UPDATE 或 Delete 。
交易状态列表示成功或失败
Transaction column defines the type of transaction. Can be INSERT, UPDATE or DELETE.
Transaction_Status column indicates SUCCESS or FAILURE
如何实现此目标？
我的触发器：
Alter Trigger TR_test
ON subscribers
FOR UPDATE
AS BEGIN
DECLARE @OldValue xml,@NewValue xml, @changedby varchar(50), @ReferenceId int
-----------------------------------------------------------------------------
SELECT @OldValue=b.username, @NewValue=a.username,
@ReferenceId = a.user_id, @changedby = a.modified_by
FROM inserted a, deleted b;
-----------------------------------------------------------------------------
INSERT INTO [dbo].[audit_log]
([old_value],[new_value],[module],[reference_id],[transaction]
,[transaction_status],[stack_trace],[modified_on],[modified_by])
VALUES
(@OldValue,@NewValue,'Subscriber',@ReferenceId,'_transaction',
'_transaction_status','_stack_trace',getdate(),555)
-----------------------------------------------------------------------------
END
推荐答案
一旦修复触发器以覆盖所有三个操作，
Once you fix your trigger to cover all three operations,
IF EXISTS (SELECT 1 FROM inserted)
BEGIN
IF EXISTS (SELECT 1 FROM deleted)
BEGIN
SET @action = 'UPDATE';
END
ELSE
BEGIN
SET @action = 'INSERT';
END
ELSE
BEGIN
SET @action = 'DELETE';
END
另一种选择是三个单独的触发器，每个动作一个。
Another alternative is three separate triggers, one for each action.
请谨慎使用MERGE ...或者在移至SQL Server 2008或更高版本时为它做好准备。
Be wary of MERGE though if you are using it... Or be prepared for it when you move to SQL Server 2008 or beyond.
编辑
我认为您可能只是对失去了而是触发（具有讽刺意味的）。这是一个例子。让我们考虑一个具有PK列和唯一列的非常简单的表：
I think what you may be after is an INSTEAD OF trigger instead (how ironic). Here is one example. Let's consider a very simple table with a PK column and a unique column:
CREATE TABLE dbo.foobar(id INT PRIMARY KEY, x CHAR(1) UNIQUE);
GO
还有一个简单的日志表来捕获活动：
And a simple log table to catch activity:
CREATE TABLE dbo.myLog
(
foobar_id INT,
oldValue XML,
newValue XML,
[action] CHAR(6),
success BIT
);
GO
以下 INSTEAD OF 触发器将拦截 INSERT / UPDATE / DELETE 命令，尝试复制其应完成的工作，并记录是失败还是成功：
The following INSTEAD OF trigger will intercept INSERT/UPDATE/DELETE commands, attempt to replicate the work they would have done, and log whether it was a failure or success:
CREATE TRIGGER dbo.foobar_inst
ON dbo.foobar
INSTEAD OF INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @action CHAR(6), @success BIT;
SELECT @action = 'DELETE', @success = 1;
IF EXISTS (SELECT 1 FROM inserted)
BEGIN
IF EXISTS (SELECT 1 FROM deleted)
SET @action = 'UPDATE';
ELSE
SET @action = 'INSERT';
END
BEGIN TRY
IF @action = 'INSERT'
INSERT dbo.foobar(id, x) SELECT id, x FROM inserted;
IF @action = 'UPDATE'
UPDATE f SET x = i.x FROM dbo.foobar AS f
INNER JOIN inserted AS i ON f.id = i.id;
IF @action = 'DELETE'
DELETE f FROM dbo.foobar AS f
INNER JOIN inserted AS i ON f.id = i.id;
END TRY
BEGIN CATCH
ROLLBACK; -- key part here!
SET @success = 0;
END CATCH
IF @action = 'INSERT'
INSERT dbo.myLog SELECT i.id, NULL,
(SELECT * FROM inserted WHERE id = i.id FOR XML PATH),
@action, @success FROM inserted AS i;
IF @action = 'UPDATE'
INSERT dbo.myLog SELECT i.id,
(SELECT * FROM deleted WHERE id = i.id FOR XML PATH),
(SELECT * FROM inserted WHERE id = i.id FOR XML PATH),
@action, @success FROM inserted AS i;
IF @action = 'DELETE'
INSERT dbo.myLog SELECT d.id,
(SELECT * FROM deleted WHERE id = d.id FOR XML PATH),
NULL, @action, @success FROM deleted AS d;
END
GO
让我们尝试一些非常简单的隐式事务语句：
Let's try some very simple, implicit-transaction statements:
-- these succeed:
INSERT dbo.foobar SELECT 1, 'x';
GO
INSERT dbo.foobar SELECT 2, 'y';
GO
-- fails with PK violation:
INSERT dbo.foobar SELECT 1, 'z';
GO
-- fails with UQ violation:
UPDATE dbo.foobar SET x = 'y' WHERE id = 1;
GO
检查日志：
SELECT foobar_id, oldValue, newValue, action, success FROM dbo.myLog;
结果：
foobar_id oldValue newValue action success
--------- ----------------------------- ----------------------------- ------ -------
1 NULL <row><id>1</id><x>x</x></row> INSERT 1
2 NULL <row><id>2</id><x>y</x></row> INSERT 1
1 NULL <row><id>1</id><x>z</x></row> INSERT 0
1 <row><id>1</id><x>x</x></row> <row><id>1</id><x>y</x></row> UPDATE 0
当然，您可能希望日志表中的其他列，例如用户，日期/时间，甚至是原始声明。这并不意味着要成为一个全面的审计解决方案，仅是示例。
Of course you probably want other columns on the log table, such as user, date/time, maybe even the original statement. This wasn't meant to be a fully comprehensive auditing solution, just an example.
正如Mikael指出的那样，这依赖于外部批处理是一个命令的事实。启动隐式事务。如果外部批处理是显式的多语句事务，则必须测试该行为。
As Mikael points out, this relies on the fact that the outer batch is a single command that starts an implicit transaction. The behavior will have to be tested if the outer batch is an explicit, multi-statement transaction.
另外请注意，在以下情况下，这不会捕获失败：例如，UPDATE影响零行。因此，您需要明确定义故障的含义-在某些情况下，您可能需要在外部代码中而不是在触发器中构建自己的故障处理。
Also note that this does not capture "failure" in the case where, say, an UPDATE affects zero rows. So you need to explicitly define what "failure" means - in some cases you may need to build your own failure handling in the outer code, not in a trigger.
这篇关于在SQL Server中触发-获取为审核表完成的事务类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
