Duplicate Key Error in SOP10106 1

[bustercoder]

OK, this one has me pulling what little hair I have left OUT for the past two days. If someone can offer a solution I would be most obliged. OK, so I have this trigger on the SOP10100 table which fires on inserted:
CREATE TRIGGER [dbo].[trig_updateUserDefined] ON [dbo].[SOP10100]
FOR INSERT, UPDATE, DELETE
AS
begin
SET NOCOUNT ON

if exists (select * from deleted)
begin
delete SOP10106
from SOP10106 utc inner join
deleted on (utc.SOPNUMBE = deleted.SOPNUMBE) where deleted.soptype = 2
end

if exists (select * from inserted)
begin
insert SOP10106
select SOPTYPE, SOPNUMBE, '', '', PYMTRMID, '', '', 'TBZ SEND PENDING', '', '', '', '', '', '', '', '', COMMNTID
from inserted where inserted.soptype = 2
end
end

So, it actually works great, EXCEPT, when they transfer a BACK ORDER to and ORDER, this insert is clashing with a GP update/insert in the SOP10106 table:

"Violation of PRIMARY KEY constraint 'PKSOP10106'. Cannot insert duplicate key in object 'SOP10106'."

I've tried changing the trigger to include an update (if exists in both tables, then update, etc.) at which time I get "Cannot update SOP_HDR. A lock on the record could not be established". Seems that no matter what I try, I just cannot get around this error when a Back Order is transferred to an Order. So, if anyone has any suggestions, I would really appreciate it. BTW - it has to be a trigger and cannot be an update in a scheduled job, they need to see the value in the user defined field right away after they save the order.

Thanks,
Buster

 

[RobertC22]

As it appears that every record in the SOP10100 has a matching SOP10106 record, how about a trigger on the SOP10106 itself?

Here is how I would suggest it would be coded.
This does assume that DynamicsGP is saving the SOP10100 record first, then the SOP10106 for a new SOP document.

create trigger dbo.trSOP10106_UpdateUDF on SOP10106
FOR INSERT, UPDATE
AS
Begin
	update udf
	set USRTAB01 = H.PYMTRMID
	, USRDEF1 = 'TBZ SEND PENDING'
	, CMMTTEXT = COMMNTID
	from SOP10106 udf 
		inner join inserted ins on ins.SOPTYPE = udf.SOPTYPE and ins.SOPNUMBE = udf.SOPNUMBE
		inner join SOP10100 H on ins.SOPTYPE = H.SOPTYPE and ins.SOPNUMBE = H.SOPNUMBE
	where ins.SOPTYPE = 2
end

------
Robert

 

[bustercoder]

Thanks Robert!

Much obliged. I'll test it out in my dev env. I really appreciate your help.

Buster

 

[bustercoder]

Robert,

Arrghhh.....when I transfer the order to a back order, then that back order back to an order again, its still giving me that "Violation of PRIMARY KEY constraint 'PKSOP10106'. Cannot insert duplicate key in object 'SOP10106'" error.

I don't understand why because the trigger on the sop10106 is doing an update.

Any suggestions?

Thanks,
Buster

 

[RobertC22]

Have you disabled or removed your original trigger on table SOP10100?

------
Robert

 

[bustercoder]

Hi Robert,

Yes, but the problem is that this trigger assumes a record will always be inserted into the SOP10106 by means of the user actually going into the user defined window and adding a value, which is usually not the case. In this case, I have to make sure there is an insert into the SOP10106 table when an order is saved, even if there is nothing entered in the user defined window. If there was always a record automatically inserted on order entry then i would have no problem and this would work.

Thanks,
Buster

 

[RobertC22]

Buster,

Is it feasible to add VBA code onto the SOP entry window?

The code would run when the user hits [SAVE] button.
The VBA code opens the User Defined Fields window (but hidden), so then the dex code on the SAVE button would recognise that the UDF window has been opened.


------
Robert

 

[RobertC22]

Buster,

Another option may be an INSTEAD OF trigger on the SOP10106 in conjunction with the trigger on SOP10100.

However, as I have not used INSTEAD OF triggers, I cannot offer how that would be codded.
I believe that with the INSTEAD OF trigger on the SOP10106 you could check if the record already existed when attempting an insert so that it would avoid the Primary Key Violation error.

------
Robert

 

[bustercoder]

Robert,

Actually, I've tried what you're suggesting with the INSTEAD OF trigger with exists and not exists. It seems that I might hve to go the way of the VBA. A couple other folks I've spoken with have said this might be the only solution, seeing as how an itial insert is required in all cases. I was hoping to avoid that, but I might not be able to.

Thanks,
Bruce