March 28, 2006 at 10:20 am
Hi all,
I have a question? Whether Sql supports mutlithreads on triggers.
For example:
I have a Patient table. When two patient records getting inserted by two different transactions does it trigger the triggers in different threads and execute the code in Insert trigger Or only one row will fire the trigger and the other one won't?
Thanks
March 28, 2006 at 12:01 pm
I've got a related question.
Lets say that I have an insert trigger on table A that updates information in table B, then inserts into table C
I've got a process (written in VB.NET) that should be able to load data from multiple data sources into table A.
The first process starts up fine, I get a throughput of about 150 records a second (which for the size of the table is reasonable).
The second I try and startup another process pulling from a different data source, I get the following error:
[Microsoft][SQL Native Client][SQL Server]The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
I know it isn't the database itself because when I disable the trigger everything is fine. I'm not doing anything trick in the trigger either.
The table I'm inserting into has a surrogate key, and the tables children arrive AHEAD OF the parent. So, the trigger looks into the child table, updates the child with the key, then moves the record out of the staging table and into the actual "reporting" table.
IT IS CRITICAL that my application be capable of handling multiple simultaneous inserts. Any ideas on how to get the trigger to either:
a) spawn threads/instances of itself so that it can handle multiple simultaneous inserts
b) Do something with the engine (setting some parameter that I haven't found perhaps) so that it will not simply crash any other inbound processes to allow for concurrent operations that way.
Any help is greatly appreciated.
March 28, 2006 at 12:29 pm
I can help you if you could send me the code that you have used for trigger?
March 29, 2006 at 2:18 am
Triggers fire within the connection and the transaction that did the initial insert/update/delete. If 4 users all update different records of a table and there's an update trigger on the table, the trigger will fire for each update.
Note that if a single update updates multiple records then the trigger fires only once. It does not fire for each record updated.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 29, 2006 at 2:24 am
There's something wierd going on there. Please post the trigger code and the vb code that you call the inserts from.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply