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?
 
BillKollatosConnect With a Mentor Author 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
 
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
All Courses

From novice to tech pro — start learning today.