October 23, 2013 at 9:19 pm
Hi, I've created a trigger which copies data from table ENBRH to table AMBUDG if a specific column in table ENBRH is updated.
ENBRH contains Asset approval requests. Once this request is approved, an approval number is created in column ENBRH.IDDOC.
Once this approval number is created I want to copy it to table AMBUDG.
Asset requests which have not been approved show ***NEW*** in ENBRH.IDDOC.
The error I'm getting is "Violation of PRIMARY KEY constraint 'AMBUDG_KEY_0'. Cannot insert duplicate key in object 'dbo.AMBUDG'"
My table ENBRH looks like this:
ID | IDDOC | DESCRIPTION
1 | ***NEW*** | Asset 1
2 | ***NEW*** | Asset 2
3 | CPX-OPT-201310-00051 | Asset 3
My table AMBUDG looks like this
Code | Description
OPT1310051| CPX-OPT-201310-00051
This is my trigger
ALTER TRIGGER [dbo].[Create_Budget_Codes]
ON [dbo].[ENBRH]
AFTER UPDATE
AS
IF UPDATE (IDDOC)
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--only update when capex number is created
IF (SELECT LEFT(IDDOC,3) FROM INSERTED) <> 'CPX'
BEGIN
RETURN
END
-- Insert statements for trigger here
INSERT INTO AMBUDG
SELECTSUBSTRING(IDDOC, 5, 3) + SUBSTRING(IDDOC, 11, 4) + RIGHT(RTRIM(IDDOC), 3), IDDOC
FROM INSERTED
END
If I now approve the request with ID 1 in table ENBRH, I get above error message, although the record does not yet exist in AMBUDG.
I think it might have something to do with ***NEW*** but don't know how to check it.
Can I somehow view what the duplictae record is?
I cannot find any duplicate record it is trying to create.
October 24, 2013 at 4:34 am
A couple of thoughts:
1. Your trigger is not safe, as it assumes only one row is processed at a time.
IF (SELECT LEFT(IDDOC,3) FROM INSERTED) <> 'CPX'
This will break horribly when multiple rows are updated.
Do something similar to this instead:
INSERT INTO AMBUDG
SELECT SUBSTRING(IDDOC, 5, 3) + SUBSTRING(IDDOC, 11, 4) + RIGHT(RTRIM(IDDOC), 3), IDDOC
FROM INSERTED
WHERE LEFT(IDDOC,3) = 'CPX'
2. The duplicate key(s) can only come from the data you are inserting and the data that is already in the table.
If you are sure that the key you are inserting is not already in the table, then the only other possibility is that the data you are inserting contains duplicates.
Debug the trigger with the debugger (not in production!!!!) or use some PRINT statements to capture the duplicates.
Hope this helps
Gianluca
-- Gianluca Sartori
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply