bustercoder
Programmer
- Mar 13, 2007
- 96
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
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