SQL Server - 插入后/用于插入 - 回滚

问题描述：

我有以下触发器:

  CREATE Trigger instructor_expertise on CourseSections
     After Insert
     As Begin
     ......

     If (Not Exists(Select AreaName From AreasOfInstructor Where (InstructorNo = @InstructorNo AND AreaName = @AreaName))) 
     Begin 
     RAISERROR('Course not in instructors expertise', 16, 1)
     rollback transaction  
     End
     GO

我的问题是，回滚事务"会删除该行吗?如果它是For Insert"，那么在这种情况下，回滚事务"会删除该行吗?

My question is, does 'rollback transaction' remove the row? What if it's 'For Insert' instead, does 'rollback transaction' remove the row in that case?

谢谢！！！

答

你的 INSERT 语句总是在一个事务中运行——要么你已经明确定义了一个，或者如果没有，那么 SQL Server 将使用隐式交易.

Your INSERT statement always runs in a transaction - either you've explicitly defined one, or if not, then SQL Server will use an implicit transaction.

您要在表格中插入一行(或多行).然后 - 仍然在事务内部 - AFTER INSERT 触发器运行并检查某些条件 - 通常使用触发器内部可用的 Inserted 伪表，其中包含已插入的行.

You're inserting one (or multiple) row into your table. Then - still inside the transaction - the AFTER INSERT trigger runs and checks certain conditions - typically using the Inserted pseudo table available inside the trigger, which contains the rows that have been inserted.

如果您在触发器中调用 ROLLBACK TRANSACTION，那么是的 - 您的交易，连同它所做的一切，都回滚，就好像 INSERT 从未发生过 - 您的数据库表中没有显示任何内容.

If you call ROLLBACK TRANSACTION in your trigger, then yes - your transaction, with everything it's been doing, is rolled back and it's as if that INSERT never happened - nothing shows up in your database table.

另外:FOR INSERT 与 SQL Server 中的 AFTER INSERT 相同 - 触发器在 INSERT 之后执行code> 语句完成了它的工作.

Also: FOR INSERT is the same as AFTER INSERT in SQL Server - the trigger is executed after the INSERT statement has done its job.

要记住的一件事(很多程序员都弄错了):触发器每条语句触发一次 - 不是每行触发一次！因此，如果您一次插入 20 行，触发器将被触发一次，并且触发器内的 Inserted 伪表包含 20 行.您在编写触发器时需要考虑到这一点 - 您并非总是只处理插入的一行！

One thing to keep in mind (which a lot of programmers get wrong): the trigger is fired once per statement - NOT once per row! So if you insert 20 rows at once, the trigger is fired once and the Inserted pseudo table inside the trigger contains 20 rows. You need to take that into account when writing the trigger - you're not always dealing with just a single row being inserted!

SQL Server - 插入后/用于插入 - 回滚

相关推荐