Update Trigger Write Conflict

[TheWebDataGuy]

I have an update trigger on one of my SQL tables. In the adp client there is a form that is bound to the table. Now since adding the trigger, the form gives me a "Write Conflict" error whenever I try to update any information on the form. If I remove the trigger all works fine. The trigger makes an update in a different table so I don't know why is there a write conflict.

Any ideas??

Thanks,

 

[cmmrfrds]

Need more information to give an opinion. What tables are bound to your form - table names. Paste in the code for your trigger. What constraints are on the tables in question i.e. foreign key primary key relationships. This should be a good start.

 

[Crookshanks]

I seem to have a similar problem. With an ADP front-end and an SQL server back-end, in the adp a table tblPerson is updated. A trigger in SQL server then inserts 5 records into a table tblPersonProject in which a FK points to tblPerson. This table is not opened by the ADP. When I manually update a record in SQL server, the trigger works correctly, however when updating a record in ADP one of 2 things seem to happen:

- It works correctly, or
- I get a message: "Key column inforamtion is insufficient or incorrect. Too many rows were affected by update". This message is then followed by a write conflict.

The same problem happens when I open the table tblPersonProject and delete all 5 records for that person.

 

[cmmrfrds]

Paste in the code for the trigger.

 

[Crookshanks]

My trigger is:

CREATE TRIGGER newLogon
ON dbo.tblPerson
FOR update AS
IF UPDATE(sLogon)
BEGIN
IF NOT EXISTS( SELECT a.* FROM tblPersonProject AS a, inserted AS b WHERE a.lPersonID=b.lPersonID)

INSERT INTO dbo.tblPersonProject
SELECT inserted.lPersonID, tblProject.lProjectID , 0 As bGesloten FROM inserted, tblProject
WHERE sProject IN ('Algemeen', 'Bezoek Arts', 'Buitengewoon verlof', 'Cursus volgen',
'Feestdag', 'Verlof/TvT', 'Ziekte')
END

It will insert the result of a cartesian product between up to seven default projects and the id of the person for whom the logon was changed.

 

[cmmrfrds]

do you have a constraint on lPersonID? What is the relationship?

 

[Crookshanks]

No. lPersonID is the PK (+ identity) of tblPerson. There is a relationship between tblPerson (PK table) and tblPersonProject (FK table) on lPersonID. There is a realtionship between tblProject (PK table) and tblPersonProject (FK table) on lProjectID. Both relationships have the option 'Enforce for inserts and updates' checked on.

When being tested in sql server, the trigger works as desired.

 

[cmmrfrds]

There is a Forum on tek-tips called microsoft sql server programming. Probably ask them, how is it possible to get the error message considering the code in the trigger. Also, reference this thread on the post to make it easier for them.

 

[cpark87]

Were you able to find a solution this problem. I am encountering the same issue.

 

[Crookshanks]

Just back from holiday, so I have not been working on the problem.

I saw the thread you started in the SQL server Forum, we indeed seem to have the same problem.

 

[cpark87]

I did want to mention that even know I got the error while trying to do the update, the changes did take affect. I also wanted to mention I received this error only in Access Project and not in Access 2002, 2003 or SQL Server Enterprise Manager.

Ive decided to go with a different method on this but if you find any answers let me know. I would be curious to find an answer for this.


cmp....

 

[Crookshanks]

I built a stored procedure that adds the 7 rows, and programmed my VB code to call it.

So, the original problem remains unsolved.

 

[ParDeek]

I use ADP with triggers on virtually every table and have not had this problem. However I had occasion to write some functionality in VB6 and got exactly this message. After many days of effort I included the line

set ansi_warnings off

at the beginning of each trigger and it cured the problem. You should probably have

set nocount on

as well.

 

[bmc1976]

GOD BLESS YOU!!! IT WORKS!

 

[cpark87]

Have any of you run into this before.. I have an insert trigger on a table that takes the inserted record and copies all of the fields into an audit table to keep track of when someone inserts a record. However when using Access ADP and SQL Server I get an error on the table with the insert trigger. It apparently tries to insert the record into the audit table before a primary key is established. Like I said this only happens in access and not when using SQL or inserting a record through a web page like ASP. Any suggestions

 

[MaxEd]

cpark87,

Add a "REFRESH" on a line before your INSERT. That should solve your problem.

-Laughter works miracles.

 

[cpark87]

Refresh does not seem to be a valid command in a SQL Server Trigger. Do you have any additional info on the refresh command

 

[cmmrfrds]

If you have an identity field on the sql server table the field should be available in the trigger. Paste in the trigger code.

 

[cpark87]

Here is the trigger code:

Create TRIGGER tangram.Insert_tblIssueLog
ON tangram.tblIssueLog

FOR INSERT
AS


Insert into tangram.tblIssueLog_Audit (a_IssueID, a_DivisionID, a_DeptID, a_CategoryDesc, a_Issue_Desc, a_Contact,
a_DateFirstReported, a_Est_ResolutionDate, a_Actual_ResolutionDate, a_Resolution_Desc, a_CreatedBy, a_DateCreated,
a_LastUpdatedBy, a_DateLastUpdated, a_Action)
Select issueID, DivisionID, DeptID, CategoryDesc, Issue_Desc, Contact,
DateFirstReported, Est_ResolutionDate, Actual_ResolutionDate, Resolution_Desc, CreatedBy, DateCreated,
LastUpdatedBy, DateLastUpdated,'INSERTED'
From INSERTED

As I stated earlier.. I only have this problem when using an Access ADP file. If this is done in SQL or through an ASP page it works fine.

 

[cmmrfrds]

You are pulling the data from the INSERTED record which will only be available after the record has been inserted with the identity column filled in. I don't see how this is the problem. Is a_IssueID the identity column?

How are you running the SP? Through an ADO Command?
What are the cursor settings?