从触发器回滚事务

Question

在 MS SQL Server 2008 R2 中，我们想要一个预插入和预更新触发器来检查某些内容，并允许或回滚（通过 raiserror）正在运行的插入/更新操作。

问题：在 INSTEAD OF 触发器中，是否真的需要显式编写插入或更新操作？因为我们希望默认的插入或更新操作能够执行，并且只对其进行“预检查”。

- Cartesius00

---

“precheck”的本质是什么？触发器确实需要管理“inserted”/“deleted”表，这会带来一些开销。是否有其他可以强制执行的方法？ - Martin Smith

我们所需要的是在三列上设置唯一约束，忽略其中一列中的空字符串。 - Cartesius00

那么对于元组 (a,b,c)，如果 c 的值为空字符串，您希望完全忽略该元组以满足唯一约束的目的？ - Martin Smith

@MartinSmith：是的！就是这样。我们希望三元组(a,b,c)在c非空时是唯一的。 - Cartesius00

在这种情况下，您可以使用一个唯一的过滤索引 CREATE UNIQUE INDEX ix ON T(a,b,c) WHERE c <> ''。 - Martin Smith

即使那不是一个选项，按照Martin Smith所说，一个在插入/更新之后的触发器可以回滚事务，即使没有显式事务，也会回滚插入/更新。 - user743382

Answer 1

是的。

您确实需要编写明确的INSERT或UPDATE代码。

触发器在执行DML操作时运行INSTEAD OF。如果您将触发器留空，则除了在tempdb中创建和填充INSERTED/DELETED表之外，不会发生任何操作。

尽管从评论讨论中可以得知，我不建议使用触发器，而是使用唯一的筛选索引CREATE UNIQUE INDEX ix ON T(a,b,c) WHERE c <> ''。这可能更有效，并避免处理并发时潜在的逻辑问题。

- Martin Smith

---

谢谢。那么在 DML 操作中插入和更新的行的别名是什么？ - Cartesius00

@James - INSERTED和DELETED是表而不是行。触发器每个语句只会触发一次。因此，INSERT INTO YourTable SELECT * FROM INSERTED是一个典型的INSTEAD OF INSERT触发器。 - Martin Smith

如果 YourTable 包含标识列和许多其他列，那怎么办呢？SELECT * 就成了问题，不是吗？ - Cartesius00

@James - 是的。IDENTITY列和其他类型的计算列需要从SELECT和INSERT列表中排除。 - Martin Smith

MySQL不支持部分/过滤的唯一索引 :( - mecampbellsoup

Answer 2

如果您不想替换实际的插入或更新操作，那么您可能不需要使用 INSTEAD OF 触发器。在您的情况下，您需要使用 FOR INSERT, UPDATE 触发器。

这个例子中的触发器在任何人尝试添加或更改 titles 表中的数据时向客户端打印一条消息。

USE pubs
IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'reminder' AND type = 'TR')
   DROP TRIGGER reminder
GO
CREATE TRIGGER reminder
ON titles
FOR INSERT, UPDATE 
AS RAISERROR ('inserts and updates to the titles table is not allowed', 16, 1)
GO

你也可以使用 IF EXISTS 或者 COLUMNS_UPDATED。下面是一个使用回滚的例子。

USE pubs
IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'employee_insupd' AND type = 'TR')
   DROP TRIGGER employee_insupd
GO
CREATE TRIGGER employee_insupd
ON employee
FOR INSERT, UPDATE
AS
/* Get the range of level for this job type from the jobs table. */
DECLARE @min_lvl tinyint,
   @max_lvl tinyint,
   @emp_lvl tinyint,
   @job_id smallint
SELECT @min_lvl = min_lvl, 
   @max_lvl = max_lvl, 
   @emp_lvl = i.job_lvl,
   @job_id = i.job_id
FROM employee e INNER JOIN inserted i ON e.emp_id = i.emp_id 
   JOIN jobs j ON j.job_id = i.job_id
IF (@job_id = 1) and (@emp_lvl <> 10) 
BEGIN
   RAISERROR ('Job id 1 expects the default level of 10.', 16, 1)
   ROLLBACK TRANSACTION
END
ELSE
IF NOT (@emp_lvl BETWEEN @min_lvl AND @max_lvl)
BEGIN
   RAISERROR ('The level for job_id:%d should be between %d and %d.',
      16, 1, @job_id, @min_lvl, @max_lvl)
   ROLLBACK TRANSACTION
END

我不确定您是否有交易，但在您的情况下，您需要类似以下的东西:

USE myDatabase

    IF EXISTS (SELECT name FROM sysobjects
          WHERE name = 'myTable' AND type = 'TR')
       DROP TRIGGER tr_myTrigger
    GO
    CREATE TRIGGER tr_myTrigger
    ON myTable
    FOR INSERT, UPDATE
    AS

if(exists(select * from inserted where rtrim(c) <> ''))
begin
  -- check to make sure the insert(s) are unique

    if(exists(
      select * from inserted i
      join dbo.myTable t on i.a = t.a and i.b = t.b and i.c = t.c)

    begin
      raiserror('Duplicate(s) found', 16, 1)
      rollback transaction
    end
end

- Chris Gessler