| Author |
Topic |
|
mdelgado
Posting Yak Master
141 Posts |
 Posted - 2003-11-20 : 14:58:01
|
| Hello all....I'm having a problem with a trigger. I have a table that has an insert trigger (Trigger-1) which updates one of the columns in the table. After Trigger-1 fires, I have another trigger (Trigger-2) that sends an Email explaining the latest changes made to the table and all of this works fine.This morning, I tried adding to Trigger-2 an exec statement that performs a BCP call and exports the table into a .txt file to a shared folder on another server. After I added this change, Locks are created on the server and I can't figure out why... I've even created a sproc that runs BCP and exports the file and tried calling that from Trigger-2 with no luck. I've also modified the trigger to an "After Insert" trigger and that doesn't work either.If I run the BCP statement outside of the trigger, it works fine, it's only when I run it inside the trigger that all the locks happen.Help please....thanks. |
|
|
ToddV
Posting Yak Master
218 Posts |
Posted - 2003-11-20 : 15:36:18
|
| This is my understanding.. Please excuse me If I don't hit this all right on the nose.During the running of the trigger, you have some locks on the table as a result of the insert. These locks are ignored as long as you are operating in the same seesion. BCP however would use a seperate session to connect to the DB and the locks would be have to be observed. I would think this is probably along the lines of where you are having the problem.You could start a Job from the trigger that called your procedure to bcp data out. That should work. The Job would wait for the Insert to finish and then run. Maybe someone has a better suggestion. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-11-20 : 15:39:04
|
| Triggers execute within a transaction. You should keep the trigger code as simple as possible so that the trigger can complete quickly.Tara |
|
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2003-11-20 : 16:11:44
|
| Not sure..but try to write into body of the 2nd trigger commit tran (as first line). |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-11-20 : 16:17:27
|
quote:
Originally posted by Stoad
Not sure..but try to write into body of the 2nd trigger commit tran (as first line).
You do not want to do this. You want them to execute within one transaction so that if a problem is encountered the entire thing rolls back. If you commit what has already occurred and then more stuff happens but then fails, then you will not be able to rollback the entire thing only the last part.Tara |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-11-20 : 16:25:04
|
| Triggers send email and bcping data?Pretty soon they'll be making margartias!Brett8-) |
|
|
|
ToddV
Posting Yak Master
218 Posts |
Posted - 2003-11-20 : 16:26:21
|
| To get around this..maybe try the following. Create a user defined error with logging. make sure the severity is such that it will not blow up your transaction. see sp_addmessageCreate an alert looking for the error that starts a Job. see sp_add_alertCreate a Job that writes out your tableIn the Trigger raise the error. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-11-20 : 16:32:10
|
| Doesn't it seem odd though to unload a table after every insert?How many transaction a day go against this table?Why not unload the table after every update...every delete?Brett8-) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-11-20 : 16:34:24
|
quote:
Originally posted by X002548
Doesn't it seem odd though to unload a table after every insert?
Yes I agree. mdelgado, perhaps you should rethink this or come up with a different solution.Tara |
|
|
|
ToddV
Posting Yak Master
218 Posts |
Posted - 2003-11-20 : 16:34:44
|
| Good question. |
|
|
|
mdelgado
Posting Yak Master
141 Posts |
Posted - 2003-11-20 : 16:46:24
|
| This explanation should answer a few question and hopefully provide me with some good feedback....This table keeps track of currency conversion rates between 2 countries. Data is inserted into this table ONCE A WEEK (that's 1 insert every 7 days) as the conversion rates change. Whenever a new rate is entered by our, "Currency Coversion Guy" and Email is sent to all the key people in our accounting and mgmt departments all over the country advising them of the change. At the same time, I have to push a text file to a shared folder on one of our servers for our UNIX order entry server to pick up the file and adjust all costs to the new rate.The easiest way I thought of doing this was to wrap xp_sendmail and BCP in an "After Insert" trigger, but that's obviously not working....THe Email part works; the BCP part doesn't.thanks. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-11-20 : 16:48:43
|
| Why not let the application handle this?Tara |
|
|
|
mdelgado
Posting Yak Master
141 Posts |
Posted - 2003-11-20 : 16:50:08
|
| Because I'm the SQL guy that needs to make this work.... In SQL. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-11-20 : 16:51:42
|
| When you say bcp doesn't work, is it just that it is creating so many locks that it is affecting other users? Or does it not create the text file?Tara |
|
|
|
ToddV
Posting Yak Master
218 Posts |
Posted - 2003-11-20 : 16:54:49
|
| How do the inserts happen? SP? Can this stuff go in there?App? Call an sp to do this after all the inserts? |
|
|
|
mdelgado
Posting Yak Master
141 Posts |
Posted - 2003-11-20 : 16:57:58
|
| tduggan - When I insert into the table, the insert never happens, the application hangs and the text file is not created. The severity of the locks are so severe, that I can't even see them in Enterprise Mgr.ToddV - The inserts are done through the web. This is irrelevant though, since I was able to reproduce the error by manually enter in a record myself both through Enterprise Mgr and by running an insert startement to the table. |
|
|
|
ToddV
Posting Yak Master
218 Posts |
Posted - 2003-11-20 : 17:02:34
|
quote:
[i]ToddV - The inserts are done through the web. This is irrelevant though, since I was able to reproduce the error by manually enter in a record myself both through Enterprise Mgr and by running an insert startement to the table.
It is not irrelevant. I ask this because perhaps there is a more appropriate place for this work than a trigger. |
|
|
|
mdelgado
Posting Yak Master
141 Posts |
Posted - 2003-11-20 : 17:04:00
|
| Yes, but as I stated in my reply to tduggan earlier, that is not an option. I'm being asked to handle this process within sql. |
|
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2003-11-20 : 17:05:22
|
| Pity.. - not to be heard.. |
|
|
|
ToddV
Posting Yak Master
218 Posts |
Posted - 2003-11-20 : 17:07:31
|
| If you really can't change around how this gets in the table Create a user defined error with logging. make sure the severity is such that it will not blow up your transaction. see sp_addmessageCreate an alert looking for the error that starts a Job. see sp_add_alertCreate a Job that writes out your tableIn the Trigger raise the error. |
|
|
|
mdelgado
Posting Yak Master
141 Posts |
Posted - 2003-11-20 : 17:08:33
|
| Will try. Thank you... |
|
|
|
Next Page
|
Trigger Question....