Problem with trigger

Question

Problem with trigger

Anthony Chininea

SSC Journeyman

Points: 87
More actions
November 29, 2006 at 2:53 pm

#114764

Hi
I'm trying to integrate my CRM tool with my financial tool, both apps use SQL 2000 as the bank-end. I 've setup a table that stores the t-sql statement I want to run. A trigger on the table is used to run the t-sql statement. Here is the code used by the trigger:
CREATE TRIGGER [Run_SP] ON dbo.TblRunStoredProcedure
FOR INSERT
AS
Declare @RunID as nvarchar(32)
Declare @SP_Statement as nvarchar(4000)
Set @RunID = (SELECT ins.[Id] FROM inserted ins)
Set @SP_Statement = (SELECT cast(ins.[SP_Statement] as nvarchar(4000)) FROM inserted ins)
Commit tran
If @RunID is not Null and @SP_Statement is not null
Begin
SET XACT_ABORT ON
Execute sp_executeSQL @SP_Statement
Update TblRunStoredProcedure
Set Return_Statement = 'Statement Executed!!!'
Where [ID] = @RunID
End
The problem is that this is not working consistantly, and I do not know if the problem lies with the trigger or within the t-sql statement that's being executed. However if I run the t-sql staement via Query Analyzer it works.
I'd appreciate any help I can get.
Thanks.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 1

Move the COMMIT TRAN to before the SET statements OR modify the two SELECTs in the SET statements to ready uncommitted data with WITH (NOLOCK)...

A better question is, why are you commiting a transaction in a trigger?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ijaz SSCommitted Points: 1912 More actions · Answer 2

I recmend just remove the COMMIT TRAN, As in the current senario there is no need for this............ even though if u have this then it should be at the end of the code.

cheers

Anthony Chininea SSC Journeyman Points: 87 More actions · Answer 3

Thanks for the help,

I realized that the only solution was to remove the trigger, so I'm calling the isql utility from the client app to execute the stored procedure. So far it's working great.