April 17, 2013 at 11:30 am
I have a SQL2008R2 db that has a regular table and an audit table. I have a trigger setup so it will keep track of changes via an audit table. When an update statement is run against the regular table it fails with:
Msg 2627, Level 14, State 1, Procedure TI_JOB_POSTING_TRIGGER, Line 15
Violation of PRIMARY KEY constraint 'PK_JOB_POSTING_AUDIT_1'. Cannot insert duplicate key in object 'dbo.JOB_POSTING_AUDIT'. The duplicate key value is (548, 0589054 , Apr 17 2013 12:37PM).
THis is the statement run against the table: update job_posting set site_num=8800
Here is the regular table:
[dbo].[JOB_POSTING](
[JOB_POSTING_NUM] [int] IDENTITY(1,1) NOT NULL,
[SITE_NUM] [int] NULL,
[JOB_CAT_COD] [varchar](7) NOT NULL,
[DEFAULT_TXT_IND] [bit] NOT NULL,
[ADDRESS1_TXT] [varchar](100) NULL,
[ADDRESS2_TXT] [varchar](100) NULL,
[CITY_NAM] [varchar](50) NULL,
[ST_PROV_COD] [char](2) NULL,
[POSTAL_COD] [varchar](10) NULL,
[START_DAT] [date] NULL,
[END_DAT] [date] NULL,
[MAINT_EMP_ID] [char](11) NOT NULL,
[MAINT_TMS] [datetime] NOT NULL,
[UPLOAD_TMS] [datetime] NULL,
[POST_IND] [bit] NULL,
[COMBINATION_NUM] [int] NOT NULL,
[CREATE_EMP_ID] [char](11) NOT NULL,
[CREATE_TMS] [datetime] NOT NULL,
CONSTRAINT [PK_JOB_POSTING] PRIMARY KEY CLUSTERED
(
[JOB_POSTING_NUM] ASC
Here is the trigger:
ALTER TRIGGER [dbo].[TI_JOB_POSTING_TRIGGER] on [dbo].[JOB_POSTING] for INSERT, UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Insert into job_posting_audit
(JOB_POSTING_NUM
,SITE_NUM
,JOB_CAT_COD
,DEFAULT_TXT_IND
,ADDRESS1_TXT
,ADDRESS2_TXT
,CITY_NAM
,ST_PROV_COD
,POSTAL_COD
,START_DAT
,END_DAT
,MAINT_EMP_ID
,MAINT_TMS
,UPLOAD_TMS
,POST_IND
,COMBINATION_NUM)
SELECT JOB_POSTING_NUM
,SITE_NUM
,JOB_CAT_COD
,DEFAULT_TXT_IND
,ADDRESS1_TXT
,ADDRESS2_TXT
,CITY_NAM
,ST_PROV_COD
,POSTAL_COD
,START_DAT
,END_DAT
,MAINT_EMP_ID
,MAINT_TMS
,UPLOAD_TMS
,POST_IND
,COMBINATION_NUM
FROM inserted
END
This is my first go around with triggers... what am I doing wrong?
April 17, 2013 at 11:43 am
What is the structure of the audit table?
April 17, 2013 at 11:45 am
same as the real table...
CREATE TABLE [dbo].[JOB_POSTING_AUDIT](
[JOB_POSTING_NUM] [int] NOT NULL,
[SITE_NUM] [int] NULL,
[JOB_CAT_COD] [varchar](7) NOT NULL,
[DEFAULT_TXT_IND] [bit] NOT NULL,
[ADDRESS1_TXT] [varchar](100) NULL,
[ADDRESS2_TXT] [varchar](100) NULL,
[CITY_NAM] [varchar](50) NULL,
[ST_PROV_COD] [char](2) NULL,
[POSTAL_COD] [varchar](10) NOT NULL,
[START_DAT] [date] NOT NULL,
[END_DAT] [date] NULL,
[MAINT_EMP_ID] [char](11) NOT NULL,
[MAINT_TMS] [datetime] NOT NULL,
[UPLOAD_TMS] [datetime] NULL,
[POST_IND] [bit] NULL,
[COMBINATION_NUM] [int] NOT NULL,
CONSTRAINT [PK_JOB_POSTING_AUDIT_1] PRIMARY KEY CLUSTERED
(
[JOB_POSTING_NUM] ASC,
[MAINT_EMP_ID] ASC,
[MAINT_TMS] ASC
April 17, 2013 at 11:48 am
Can you provide the schema for the audit table?
By the looks of it you are trying to insert the JOB_POSTING_NUM column into the audit table and I imagine this is a Primary Key in the audit table? If it is then you should recreate the audit table so it has it's own identity column as the primary key then all the columns of the JOB_POSTING table which will prevent the primary key violation.
Chris
April 17, 2013 at 11:56 am
THanks a million. I made the JOB_POSTING_NUM an identity column in the audit table and removed that column from the trigger. I overlooked that 'little' detail. Just needed a second set of eyes on this one., Thanks again.:-)
April 17, 2013 at 11:57 am
CONSTRAINT [PK_JOB_POSTING_AUDIT_1] PRIMARY KEY CLUSTERED
you don't want that;
since this is an audit table, the same pk in the primary table of course would be updated multiple times.
the audit table shouldn't mirror any constraints of it's target.
remove that constraint, maybe add an ideitity() or a datetime column with a default instead.
Lowell
April 17, 2013 at 11:58 am
Great news, that is what we are here for 🙂
Chris
April 17, 2013 at 12:00 pm
Yes, I removed the Primary Key on the audit table as well.
Thanks everyone for the help.
April 17, 2013 at 12:12 pm
Well, the apps guy says this:
That column can't be an identity column in the audit table. It needs to link back to the job_posting_num column in the job_posting table. There should be a timestamp in the PK of the audit table that makes it unique.
I am at a loss now.
April 17, 2013 at 12:16 pm
Just create a foreign key between the audit table and source table on that column that will do the trick.
Chris
April 17, 2013 at 12:18 pm
if it were me, i would make it like this:
i added an identity, and a column at the end with a default of getdate() so i know when the record was created.
CREATE TABLE [dbo].[JOB_POSTING_AUDIT] (
[JOB_POSTING_AUDITID] INT IDENTITY(1,1) NOT NULL,
[JOB_POSTING_NUM] INT NOT NULL,
[SITE_NUM] INT NULL,
[JOB_CAT_COD] VARCHAR(7) NOT NULL,
[DEFAULT_TXT_IND] BIT NOT NULL,
[ADDRESS1_TXT] VARCHAR(100) NULL,
[ADDRESS2_TXT] VARCHAR(100) NULL,
[CITY_NAM] VARCHAR(50) NULL,
[ST_PROV_COD] CHAR(2) NULL,
[POSTAL_COD] VARCHAR(10) NOT NULL,
[START_DAT] DATE NOT NULL,
[END_DAT] DATE NULL,
[MAINT_EMP_ID] CHAR(11) NOT NULL,
[MAINT_TMS] DATETIME NOT NULL,
[UPLOAD_TMS] DATETIME NULL,
[POST_IND] BIT NULL,
[COMBINATION_NUM] INT NOT NULL,
[CreatedOn] DATETIME NULL DEFAULT (getdate()),
CONSTRAINT [PK__JOB_POSTING_AUDITID] PRIMARY KEY CLUSTERED ([JOB_POSTING_AUDITID]) )
Lowell
April 17, 2013 at 12:19 pm
chris.mcgowan (4/17/2013)
Just create a foreign key between the audit table and source table on that column that will do the trick.Chris
no foreign key for audit tables, usually, in my experience.
if you add a foreign key, it would need on delete set null,otherwise you could never delete int he master table.
Lowell
April 17, 2013 at 12:25 pm
True, good point.
April 17, 2013 at 12:51 pm
Great idea. I recreated the audit table just how you described and it works for inserts and updates.
However, I have the trigger selecting the first column in the identity... because we want that to carry forward into the audit table so I really don't want the first column as an identity in the audit table right? Reason thinking is that if the identity number ever got out of sync with the regular table and the audit table then that number means nothing.
April 17, 2013 at 1:06 pm
Markus (4/17/2013)
Great idea. I recreated the audit table just how you described and it works for inserts and updates.However, I have the trigger selecting the first column in the identity... because we want that to carry forward into the audit table so I really don't want the first column as an identity in the audit table right? Reason thinking is that if the identity number ever got out of sync with the regular table and the audit table then that number means nothing.
well, your trigger would be inserting into columns 2 thru 18, which exactly mirror the table you are auditing.
column 1 is just an arbitrary identityso we have a PK on the table.
column 19 would get populated by the default constraint.
if you needed to undo a transaction, you would still update like this, since you saved the PK/identity:
suppose you investigated a bad update, and neede dot rollback ten specific consecutive rows fromt eh audit table:
UPDATE MyTarget
SET MyTarget.SITE_NUM = MyAudit.SITE_NUM,
MyTarget.JOB_CAT_COD = MyAudit.JOB_CAT_COD,
MyTarget.DEFAULT_TXT_IND = MyAudit.DEFAULT_TXT_IND,
MyTarget.ADDRESS1_TXT = MyAudit.ADDRESS1_TXT,
MyTarget.ADDRESS2_TXT = MyAudit.ADDRESS2_TXT,
MyTarget.CITY_NAM = MyAudit.CITY_NAM,
MyTarget.ST_PROV_COD = MyAudit.ST_PROV_COD,
MyTarget.POSTAL_COD = MyAudit.POSTAL_COD,
MyTarget.START_DAT = MyAudit.START_DAT,
MyTarget.END_DAT = MyAudit.END_DAT,
MyTarget.MAINT_EMP_ID = MyAudit.MAINT_EMP_ID,
MyTarget.MAINT_TMS = MyAudit.MAINT_TMS,
MyTarget.UPLOAD_TMS = MyAudit.UPLOAD_TMS,
MyTarget.POST_IND = MyAudit.POST_IND,
MyTarget.COMBINATION_NUM = MyAudit.COMBINATION_NUM
FROM JOB_POSTING MyTarget
INNER JOIN JOB_POSTING_AUDIT MyAudit
ON MyTarget.JOB_POSTING_NUM = MyAudit.JOB_POSTING_NUM
--this is the investigated part i skip over
--WHERE MyAudit.CreatedOn = '2013-04-17 15:04:02.000'
WHERE MyAudit.JOB_POSTING_AUDIT BETWEEN 17451 AND 17461
Lowell
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply