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.
| Author |
Topic |
|
mwilson@g-l.co.uk
Starting Member
2 Posts |
 Posted - 2006-10-23 : 10:18:14
|
| HiI am running SQL 7.0 and have the following scenarioAn insert on Table A fires a Trigger BTrigger 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! |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-10-23 : 10:35:18
|
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 |
 |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-10-23 : 10:37:02
|
| 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). |
|
|
|
mwilson@g-l.co.uk
Starting Member
2 Posts |
Posted - 2006-10-23 : 10:43:43
|
| 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!! |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-10-23 : 10:59:19
|
| 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 |
|
|
|
TonyTheDBA
Posting Yak Master
121 Posts |
Posted - 2006-10-23 : 11:02:45
|
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.-- RegardsTony The DBA |
|
|
|
|
|
|
Trigger / Stored Procedure Help!