Is a stored procedure call inside a SQL Server tri

问题:

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.

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

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?

Here's what the trigger looks like:

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;

Thank you, kindly, for your help.

Additional question

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

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?

回答1:

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...

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