SQL Server触发器内的存储过程调用是否隐含线程安全和原子性? [英] Is a stored procedure call inside a SQL Server trigger implictly thread safe and atomic?

问题描述

我有一个SQL Server触发器.老实说，我不确定触发器是否隐式跟随ACID(Atomicity, Consistency, Isolation, Durability)，但是目前，我的触发器并没有做任何特别复杂的事情.

I have a SQL Server trigger. To be honest, I'm not quite sure if triggers implicitly follow ACID (Atomicity, Consistency, Isolation, Durability), but my trigger, at the moment, doesn't do anything particularly complex.

现在，我想从触发器中调用存储过程.我在存储过程调用和INSERT语句周围有TRANSACTION.

Now, I want to call a stored procedure from within the trigger. I have TRANSACTION surrounding the stored procedure call and an INSERT statement.

我的问题是:如果没有存储过程调用的触发器是线程安全的和原子的(至少部分是由于TRANSACTION原因)，存储过程调用是否隐式是线程安全的和原子的?

My question is: if a trigger, sans stored procedure call, is thread safe and atomic -- at least in part due to the TRANSACTION -- will the stored procedure call be implicitly thread safe and atomic?

这是触发器的样子:

CREATE TRIGGER [triggerInsert_Foobar] 
ON [Foobar]
INSTEAD OF INSERT
AS 
BEGIN
    SET NOCOUNT ON;

    -- Turns on rollack if T-SQL statement raises a run-time error
    SET XACT_ABORT ON

    -- Start new transaction
    BEGIN TRANSACTION
        -- Insert statement for trigger
        INSERT INTO Foo ( Col1, Col2 )
            SELECT 
                RTRIM ( LTRIM ( Col1 ) ), 
                Col2
            FROM 
                INSERTED

    -- Call stored procedure (takes no parameters)
    EXECUTE sp_executesql N'FoobarApp_DoSomething'

    -- Complete transaction
    COMMIT TRANSACTION
END;

谢谢您的帮助.

其他问题

已经回答了几个问题(谢谢).我事先表示歉意.

This question is being poised after a few responses already (thank you). I apologize in advance.

我的触发器和随后对存储过程的调用是否遵循ACID原则，并避免出现竞争情况和死锁?或者，是否需要在触发器和/或存储过程中添加一些内容以防止出现竞争情况和死锁?

Does my trigger and the subsequent calling of the stored procedure follow the ACID principle and avoid race conditions and deadlocks? Or, is there something I need to add to my trigger and/or the stored procedure to safeguard against race conditions and deadlocks?

推荐答案

基础操作和触发器操作被视为原子操作.他们都承诺一次交易.从文档:

The underlying operation and the trigger operation treated as atomic. They both commit in one transaction. From the documentation:

触发器和触发它的语句被视为单个事务...

The trigger and the statement that fires it are treated as a single transaction...

请注意，触发器(和SP将被调用)不会看到触发触发器的表更改.尚未提交.

Note that the trigger (and the SP is will call) wont' see the table change that fired the trigger. It hasn't committed yet.

这篇关于SQL Server触发器内的存储过程调用是否隐含线程安全和原子性?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！