Trigger Not Putting Data in History Table

Question

I have the following trigger (along with others on similar tables) that sometimes fails to put data into the historic table. It should put data into a historic table exactly as it's inserted/updated and stamped with a date.

CREATE TRIGGER [dbo].[trig_UpdateHistoricProductCustomFields]
ON [dbo].[productCustomFields]
AFTER UPDATE,INSERT
AS 
BEGIN

IF ((UPDATE(data)))
BEGIN
SET NOCOUNT ON; 

DECLARE @date bigint
SET @date = datepart(yyyy,getdate())*10000000000+datepart(mm,getdate())*100000000+datepart(dd,getdate())*1000000+datepart(hh,getdate())*10000+datepart(mi,getdate())*100+datepart(ss,getdate())

INSERT INTO historicProductCustomFields (productId,customFieldNumber,data,effectiveDate) (SELECT productId,customFieldNumber,data,@date from inserted)

END
END

Schema:

CREATE TABLE [dbo].[productCustomFields](
[id] [int] IDENTITY(1,1) NOT NULL,
[productId] [int] NOT NULL,
[customFieldNumber] [int] NOT NULL,
[data] [varchar](50) NULL,
 CONSTRAINT [PK_productCustomFields] PRIMARY KEY CLUSTERED 
(
[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[historicProductCustomFields](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[productId] [int] NOT NULL,
[customFieldNumber] [int] NOT NULL,
[data] [varchar](50) NULL,
[effectiveDate] [bigint] NOT NULL,
 CONSTRAINT [PK_historicProductCustomFields] PRIMARY KEY CLUSTERED 
(
[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON,     ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

I insert and update only on one record at a time on the productCustomFields table. It seems to work 99% of the time and hard to test for failure. Can anyone shed some light on what I may be doing wrong or better practices for this type of trigger?

Environment is Sql Server Express 2005. I haven't rolled out the service pack yet for sql server either for this particular client.

Answer 1

I think the right way to solve this is keep a TRY CATCH block when inserting into the dbo.historicProductCustomFields table and write the errors into a custom errorlog table. From there it is easy to track this down.

I also see a PK on the historicProductCustomFields table but if you insert and update a given record in ProductCustomFields table then won't you get primary key violations on the historicProductCustomFields table?

Answer 2

You should schema qualify your table that you are inserting into. You should check to ensure that there are not multiple triggers on the table, as if there are, only 1 trigger for that type of trigger will fire and if there are 2 defined, they are run in random order. In other words, 2 triggers of the same type (AFTER INSERT) then one would fire and the other would not, but you don't necessary have control as to which will fire.

Answer 3

try to use this trigger. i just give you example try to write trigger with this trigger.

create TRIGGER [dbo].[insert_Assets_Tran]
ON [dbo].[AssetMaster]
AFTER INSERT , UPDATE
AS BEGIN
DECLARE @isnum TINYINT;

SELECT @isnum = COUNT(*) FROM inserted;

IF (@isnum = 1)
INSERT INTO AssetTransaction
select [AssetId],[Brandname],[SrNo],[Modelno],[Processor],[Ram],[Hdd],[Display],[Os],[Office],[Purchasedt] ,[Expirydt],[Vendor],[VendorAMC],[Typename],[LocationName],[Empid],[CreatedBy],[CreatedOn],[ModifiedBy] ,[ModifiedOn],[Remark],[AssetStatus],[Category],[Oylstartdt],[Oylenddt],[Configuration] ,[AStatus],[Tassign]

FROM inserted;
ELSE
RAISERROR('some fields not supplied', 16, 1)
WITH SETERROR;
END