Executing a Stored Procedure from a Trigger

Question

I set up an AFTER INSERT trigger on a table that executes a stored procedure. For some reason, after an insert occurs on the table, the stored procedure doesn't run and prevents the insertion.

When I try to execute the stored procedure from new T-SQL, it works just fine.

I tried adding try catch to the trigger, but no error is caught.

I tried troubleshooting using SQL Server Profiler and I found out that the stored procedure starts running after the insert, but stops in the middle of it, although it says the statment is complete.

Any ideas what could have caused this, and how to fix it?

Answer 1

You will need to post the code for your trigger and the stored procedure as well as the table definitions in order to get the best assistance. I was able to put together this quick sample which works for me.

When you are doing the try/catch, I assume you are logging the catch to a table somewhere?

/** Cleanup **/
IF OBJECT_ID('Temp1') IS NOT NULL
    DROP TABLE Temp1;

IF OBJECT_ID('Temp2') IS NOT NULL
    DROP TABLE Temp2;

IF OBJECT_ID('TempTest') IS NOT NULL
    DROP PROCEDURE TempTest;
GO

/** Create our objects **/
CREATE TABLE Temp1 (ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY, TextValue VARCHAR(100) NULL);

CREATE TABLE Temp2
    (
        ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY
        , Temp1ID INT NULL
        , TextValue VARCHAR(100) NULL
    );
GO

CREATE PROCEDURE TempTest (@ID AS INT)
AS
BEGIN
    INSERT INTO Temp2 (Temp1ID, TextValue)
    SELECT ID
           , TextValue
    FROM Temp1
    WHERE ID = @ID;

END;
GO

CREATE TRIGGER trgTemp1After
ON Temp1
AFTER INSERT
AS
DECLARE @ID INT;

DECLARE curTest CURSOR READ_ONLY LOCAL STATIC FORWARD_ONLY FOR
    SELECT ID
    FROM INSERTED;

OPEN curTest;

FETCH NEXT FROM curTest
INTO @ID;

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC TempTest @ID;

    FETCH NEXT FROM curTest
    INTO @ID;
END;

CLOSE curTest;
DEALLOCATE curTest;
GO

/** Insert into Temp1
    Ideally, we should see the same entry appear in Temp2
    **/
INSERT INTO Temp1 (TextValue)
VALUES ('This is a test');

SELECT ID
       , TextValue
FROM Temp1;

SELECT ID
       , Temp1ID
       , TextValue
FROM Temp2;