CAN I RUN A TRIGGER TABLE WITH NO LOCKS

I am fairly new to SLQ Server and have a question regarding Triggers.

I have created a trigger table who's sole purpose is to accept records via an MS Access Append query and then perform other stored procedures on the SQL Database.

Because multiple users try to append to this table at the same time and some users run multiple appends simultaneously,  the trigger table performance struggles and I get the error "error 3155 (ODBC -- insert on a linked table 'CHECKTRIGGERTABLE' failed.)

The thing is I really dont care if the record appends to the table.  All I care about is being able to run the stores procedures with the data that is trying to be appended.

This whole logic is a band-aid solution until I can completely eliminate some 3rd party logic that needs MS Access so I can run the stored procedure directly.  But for the time being this is in production and am getting integrity issues due to the crash that I beleive is record locking.

If I remove locks from the trigger table will this allow me to execute the triggered stored procedures without any data integrity issues?

If so,  How do I do this?
BillKollatosAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rbeadieCommented:
Try using an INSTEAD OF trigger to run the stored procedure:

CREATE TRIGGER myTrig on myTable
INSTEAD OF INSERT
AS

... do stuff here ...
0
BillKollatosAuthor Commented:
Thankyou.  I will try it and let you know how I go.  I can only properly test if it makes a difference during production which will be in 20 hours from now.
0
BillKollatosAuthor Commented:
I was actually able to test it in a demonstration environment.  If I try and execute the append query through access connected to the sql server via odbc  it crashes on inserting the append into the trigger table.

Again all i need to do is run the trigger with the correct data and if the records adds onto the trigger table or not does not really matter.

My understanding is that you execute the insert command,  then the trigger runs the stored procedures with that data and then the insert commands saves the appended record to the table.

If this is the case can i remove all locks fron the trigger table?  and if so how?

The sql process command that this crashes on is the following:

(@P1 varchar(50),@P2 int,@P3 int)INSERT INTO  "dbo"."CHECKTRIGGERTABLE"  ("CheckTriggerType","FranchiseeID","SalesDTLPARCELID") VALUES (@P1,@P2,@P3)
0
rbeadieCommented:
I believe that SQL Server runs the trigger after the insert, though within the same transaction.  If the error is still occurring when you use an "instead of" trigger, then it's unlikely that deadlocking is causing the problem.  Have you tested the insert command in SQL Server Management Studio?  You should get a more specific error message that way.
0
BillKollatosAuthor Commented:
I have been unable to soolve the crash which I beleive is due to thte logic in some 3rd party software written in VB that connects to MS ACCESS to then connect to SQL.  The simulations that I run in MS ACcess to try and reproduce this error do not work which leaves me to believe the issue is in the VB code.

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.