Calling a stored procedure from a trigger

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Calling a stored procedure from a trigger

Author

Topic

ECS_Dale
Starting Member

5 Posts

Posted - 2008-11-26 : 07:36:25

Is there any way to delay the firing of a stored procedure until all updates to the table have been completed, the transaction has been committed and the records unlocked?

Allow me to explain what I am trying to achieve:

A piece of software keeps a record of customer account balances. These balances can be out of date. In order to refresh the balances on screen, the user has to click on a button on the toolbar. This is obviously quite ridiculous.

Anyway, the button merely calls a stored procdure and then refreshes the balances on-screen. I have located the stored procedure and this runs fine when run in Management Studio. "Simple!" I thought. I was planning to call it from a trigger placed on a transactions table.

The trigger should look a little like:

CREATE TRIGGER dbo.UPDATE_BALANCE ON dbo.SALES_TRANSACTIONS
AFTER INSERT
AS
BEGIN
DECLARE @Account nvarchar(10)
SELECT @Account=ACCT FROM inserted
EXEC UPDATE_BALANCE @PS_Account=@Account
END

Simple enough right? No. It seems that the stored procedure cannot be called from a trigger. I assume it is locking records or something. I have checked the code and the right value is being retrieved for the account code. It seems to update a field in the table with the correct account number anyway. The stored procedure works fine when the script is run in Management Studio (obviously hard-coding the account number in the second line).

Is there any way to get the stored procedure to fire after the transaction has been committed to the database and the records unlocked? It currently fails and rolls back. I tried a WAITFOR command to delay it by 2 seconds but all that does is queue the error for 2 seconds. Using COMMIT TRANSACTION after the SELECT line also does not resolve the problem.

Am I missing something here? I can't modify the stored procedure as it is encrypted and I cannot recreate it in the trigger for the same reason. I could perhaps write my own routine but I don't want to miss anything that the application one might do that I wouldn't have thought of and there is no way that we would be supported if I did start messing with system code like this. Also, it's a bit like taking a sledgehammer to crack a nut.

Any ideas?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-26 : 08:01:51

Dont call stored procedure from trigger.either try to include balance adjustment logic in trigger or put everything in procedure and schedule it via sql job to run at a defined periodic interval and adjust the balances

ECS_Dale
Starting Member

5 Posts

Posted - 2008-11-26 : 08:15:32

Unfortunately that's not an option as I am a 3rd party developer and do not have access to the source code. That's why I have to call the procedure.

I have had no trouble calling a stored procedure from a trigger before. Here is an example of something that works just fine:

CREATE TABLE [dbo].[A_Test](
[aValue] [int] NOT NULL,
[aSum] [int] NULL)

CREATE PROCEDURE [dbo].[aTestProc]
AS
DECLARE @Sum int
SELECT @Sum=sum(aValue) from A_Test
UPDATE A_Test SET aSum=@Sum

CREATE TRIGGER [dbo].[UpdateSum]
ON [dbo].[AA_Test]
AFTER INSERT
AS
BEGIN
EXEC aTestProc
END

INSERT INTO A_Test(aValue) VALUES(1)
INSERT INTO A_Test(aValue) VALUES(2)
INSERT INTO A_Test(aValue) VALUES(3)

SELECT * FROM A_Test

This gives:

aValue aSum
------ ----
1 6
2 6
3 6

As it should.

INSERT INTO A_Test(aValue) VALUES(4)
SELECT * FROM A_Test

Would then give the records as above with 4|10 added at the bottom and the aSum column will all say 10.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources