A trigger that does not put data in the history table

Question

I have the following trigger (along with others in similar tables) that sometimes cannot put data in a historical table. It should put the data in the historical table in the same way as it is inserted / updated and marked 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 

Scheme:

 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 one record at a time in the productCustomFields table. It seems to work in 99% of cases and is hard to check for failure. Can someone shed some light on what I can do wrong or better for this type of trigger?

The environment is Sql Server Express 2005. I have not yet posted the SQL Server Service Pack for this particular client.

Answer 1

I think the correct way to solve this is to save the TRY CATCH block when pasted into the dbo.historicProductCustomFields table and write the errors to the user error table. From there it's easy to trace it.

I also see PK in the historyProductCustomFields table, but if you insert and update this entry in the ProductCustomFields table, can you get the primary key violations in the historyProductCustomFields table?

Answer 2

You must specify the schema you are inserting into. You should check that there are not several triggers in the table, as if they were, only one trigger for this type of trigger is triggered, and if they are defined 2, they are started in random order. In other words, two triggers of the same type (AFTER INSERT), then one will shoot and the other not, but you do not need to have control over what will fire.

Answer 3

try using this trigger. I am just giving you an example to try writing a 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], [ Manufacturer], [VendorAMC], [Type Name], [LocationName], [EmpId], [CreatedBy], [CreatedOn], [ModifiedBy], [ModifiedOn], [Note], [AssetStatus], [Category], [Oylstartdt ], [Oylenddt], [Configuration], [AStatus], [Tassign]

FROM inserted
ELSE
RAISERROR ("some fields are not supplied", 16, 1)
With SETERROR,
End