December 29, 2002 at 12:48 am
My table has a trigger that fires upon insert/update. The trigger calls a extended-stored-proc which calls a ASP page which attempts to retrieve the row that was just inserted. This results in a row lock because the extended-stored-proc does not finish until the page retrieves the row..and the page cannot retrieve the row because the trigger is not complete and the row is locked.
Is there a clean solution to this without introducing a 'hack'?
December 30, 2002 at 9:11 am
Let's back up slightly. What are you trying to accomplish? Why would an ASP page need to retrieve the row within a trigger?
Steve Jones
December 30, 2002 at 11:03 am
Please, never ever put anything in trigger with exception of the simplest data integrity checking or auditing code. Triggers are not a messaging system. They are table constraints in a way.
In your case, you are locking rows exclusively for update/insert, than trying to select (create a shared lock) on rows that are already locked exclusively.
Even if it worked (impossible!), it would halt as soon as you let more then... one concurrent user on the systym. Use stored procedutre to perform your operations. Do not call an ASP page - even from a stored procedure.
December 30, 2002 at 1:15 pm
scenerio:
a row is inserted into a table, a trigger is fired which calls a custom-extended stored proc which notifies/executes an ASP page. The ASP page will then go and fetch information from the db.
The problem is that the row has an exclusive lock on it when since the trigger is waiting for the extended-stored-proc to finish executing...but it won't finish until the page grabs the row from the db (which is locked).
The ASP grabs the row in order to send this information to another system in real time. Suffice is to say that this process needs to be done! :0
ideas?
December 30, 2002 at 1:56 pm
Of course.
Instead of executing a custom XP from a trigger, insert a record into a separate 'log activity' table. Run a SQL job on the server that checks that table frequently enough. If it finds a new row, it would execute your XP and get rid of the row (or mark it as processed).
On the other hand, why use ASOP to send data from system to system? ASP is a data PRESENTATION tool, user interface tool, NOT a data transformation tool. Use SQL if your target is SQL data structure. Use ODBC, OLEDB, XML, etc. depending on the nature of your destination.
Have fun.
December 30, 2002 at 3:29 pm
ASP is active server pages which uses ODBC to connect to server.
I thought of creating another table to read from but its basically the same idea as what i'm doing now except in the current design there is no redundant data.
December 30, 2002 at 5:11 pm
If you MUST use the current method, why not move the ASP call from a trigger to a stored proc. You could insert the row in the stored proc, then commit the transaction, then call the extendend procedure that calls the ASP page...
OR
Another REALLY UGLY solution is to use the "WITH (NOLOCK)" optimizer hint when you try to pull the data back to the ASP page. This is such BAD coding practice I fear even mentioning it.
I think what it boils down to is: you're using a trigger for something it was never designed to do, and this could cause problems down the line.
-Dan
-Dan
December 31, 2002 at 5:02 am
If you can post to the other system you could instead write the XP to take the input directly for the new row and send to the remote system using MSXML see the following thread for a bit on this. http://www.sqlservercentral.com/forum/topic.asp?TOPIC_ID=8915&FORUM_ID=19&CAT_ID=3&Topic_Title=Send%20POST%20string%20to%20a%20webpage%20or%20com&Forum_Title=Data%20Transformation%20Services%20(DTS) thn either you can submit to a stroed procedure to the table an the xp or to the table and trigger the XP with the data from inserted. This will bypass the extra read needed to get the data and avoid the lock. If you do after the transaction to the table then you do not need to worry about the data as it did get insterted.
December 31, 2002 at 8:09 am
Sorry I didn't mention this before.
The trigger does this. It grabs all the information we need from the inserted table and then we create a URL string. We then pass the varchar (which contains the URL string of values we fetched from the inserted table) to a stored procedure. The stored procedure calls the ExtendedStoredProc. The ExtendedStoredProc's job is to call the ASP page and post the varchar variable that contains a name-value pair string in the url e.g .asp?name=john&lastname=smith etc... The reason we had to pass this information in the URL string instead of grabbing from the database is because the row has an exclusive lock from the trigger (as mentioned earlier).
So from what I gather we are doing the right thing. It seems Antares686 is suggesting the same thing except to use XML (haven't read the link u gave yet).
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply