SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Trigger / Stored Procedure Help!
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mwilson@g-l.co.uk
Starting Member

2 Posts

Posted - 10/23/2006 :  10:18:14  Show Profile  Reply with Quote
Hi

I am running SQL 7.0 and have the following scenario

An insert on Table A fires a Trigger B
Trigger B then executes a stored procedure C (using the params of the insert)

What I want to do is to commit all the work done on the insert before the stored procedure finishes.

Currently the work is not commited until the stored procedure has ended, which is causing timeout issues in a third party application!

Any help appreciated!

Edited by - mwilson@g-l.co.uk on 10/23/2006 10:34:50

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 10/23/2006 :  10:35:18  Show Profile  Visit spirit1's Homepage  Reply with Quote
no can do.
you want to execute everything in async. it's not how it's done.
look into speeding up your queries.

also, who guarantees that you will always have only one row updated/inserted in the trigger?
or are you using cursors?




Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

snSQL
Flowing Fount of Yak Knowledge

USA
1837 Posts

Posted - 10/23/2006 :  10:37:02  Show Profile  Reply with Quote
Not possible - by definition the trigger is firing as part of the transaction that caused the trigger to run in the first place and the trigger can only roll that transaction back, it cannot commit it. Think about it - what if the action that caused the trigger to run was one of a number of inserts, or updates, that were supposed to be part of the transaction, the trigger would then end the transaction before the others had happened.

Rather create a stored proc that performs your current action and then runs the proc that you're now calling from inside the trigger (probably after ending the transaction first).
Go to Top of Page

mwilson@g-l.co.uk
Starting Member

2 Posts

Posted - 10/23/2006 :  10:43:43  Show Profile  Reply with Quote
I was under the impression that Microsoft had addressed this in sql 2000 by using the After command on the trigger.

Basicaly there is an insert into an orders table (1 per order), I am using the trigger to fire an event which then creates a text file based on the order content.

the event outcome has no affect on the insert whatsoever!

If a trigger / stored procedure is not feasible then what other options are available.

I was thinking maybe using the trigger and the cmd_shell command to execute a batch file with the script in it!!
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 10/23/2006 :  10:59:19  Show Profile  Visit spirit1's Homepage  Reply with Quote
after trigger is meant that it runs after the insert. It's still part of that transaction. It's not async.
one option is do put the rows that have to be processed in another table in your trigger and then have a scheduled job
do the thing you want to do in the trigger now.

If you provide more accurate info on what exactly and why do you want to do, you're more likely to get a better answer.



Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

TonyTheDBA
Posting Yak Master

United Kingdom
121 Posts

Posted - 10/23/2006 :  11:02:45  Show Profile  Reply with Quote
quote:
I was thinking maybe using the trigger and the cmd_shell command to execute a batch file with the script in it!!
From what I remember of xp_cmdshell the calling procedure doesn't finish until the batch file/program completes which would probably add to your timeout issue.

What about writing the stored procedure parameter data to a table and having a scheduled job (run at an appropriate interval) that processes the rows since it last ran calling the triggered sp for each row. That way the event isn't dependant on the Insert at all.


--
Regards
Tony The DBA
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.33 seconds. Powered By: Snitz Forums 2000