SQL 从触发器回滚事务
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10072627/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Rollback transaction from trigger
提问by Cartesius00
In MS SQL Server 2008 R2, we want a pre-insert and pre-update trigger which checks something and allows or rollbacks (via raiserror) the running insert/update.
在 MS SQL Server 2008 R2 中，我们需要一个预插入和预更新触发器来检查某些内容并允许或回滚（通过raiserror）正在运行的插入/更新。
Question: In INSTEAD OFtrigger. Does one really has to explicitly write the insert or update? Because we want the default insert or update to be done and only do the "precheck".
问题：在INSTEAD OF触发器中。真的必须明确地编写插入或更新吗？因为我们希望完成默认的插入或更新，并且只进行“预检查”。
回答by Martin Smith
Yes.
是的。
You do need to write the explicit INSERTor UPDATE.
您确实需要编写显式INSERT或UPDATE.
The trigger runs INSTEAD OFthe DML operation. If you leave the trigger blank then no action will happen other than the INSERTED/ DELETEDtables being created and populated in tempdb.
触发器运行INSTEAD OFDML 操作。如果您将触发空白，则比不采取行动会发生其它INSERTED/DELETED创建和填充表tempdb。
Although from discussion in the comments I would not use a trigger for this at all but use a unique filtered index CREATE UNIQUE INDEX ix ON T(a,b,c) WHERE c <> ''. This is likely to be more performant and avoid potential logic issues when dealing with concurrency.
尽管根据评论中的讨论，我根本不会为此使用触发器，而是使用唯一的过滤索引CREATE UNIQUE INDEX ix ON T(a,b,c) WHERE c <> ''。这可能会提高性能，并在处理并发时避免潜在的逻辑问题。
回答by Chris Gessler
You probably do not want an INSTEAD OFtrigger unless you want to replace the actual insert or update. In your case, you want a FOR INSERT, UPDATEtrigger instead.
INSTEAD OF除非您想替换实际的插入或更新，否则您可能不想要触发器。在你的情况下，你想要一个FOR INSERT, UPDATE触发器。
This example trigger prints a message to the client when anyone tries to add or change data in the titles table.
当有人尝试添加或更改 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
You could also use things like IF EXISTSor COLUMNS_UPDATEDas well.
你也可以使用像IF EXISTS或COLUMNS_UPDATED一样的东西。
Here another example that uses rollback.
这是另一个使用回滚的示例。
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
I'm not sure if you have a transaction or not, but in your case you would want something like the following:
我不确定您是否有交易，但在您的情况下，您会想要以下内容：
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
