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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-11-07 : 09:24:44
|
| Ashish writes "Hi, I am using SQL Server 7.0 SP4 on Windows NT 4 server with SP6. I have a situation where I have following setup:TABLE A having insert trigger ( which will insert some datain TABLE B )TABLE B having insert trigger to create a COM object to letit do its deed. Problem, I am facing is that while inserting something in TABLE A following will take place: 1. Table A will fire trigger which will attemp to insert data in TABLE B. 2. TABLE B will fire trigger which will call COM object to do its deed. 3. Mean while TABLE A's insert trigger is waiting to complete which is hold up by COM to execute. I want someway to make TABLE A's trigger unserstand that it's job is finished after trying to insert data into TABLE B. So that it does not haveto wait form COM to complete its work.... Is there a way to do it... I am sure there has to be one... but I am looking in wrong direction... Help me, please!" |
|
|
smccreadie
Aged Yak Warrior
505 Posts |
Posted - 2002-11-07 : 09:39:04
|
| A trigger is run within the scope of what calls it and the actions of that trigger. In short, you shouldn't use a trigger if the result will take a bunch of time to complete.One way I've worked around this is to use jobs. Use your insert to flag that something needs to be done and have the job pick it up. |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-11-07 : 09:41:19
|
| Table A'a trigger hasn't completed it's work. If the trigger on B fails it will have to roll back the transaction so it cannot complete before B's trigger completes.It is a bad idea to call a com object in a trigger due to this locking problem.Can A's trigger put the request into a table and a scheduled job look at that table and call the com object?==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
Trigger Situation