Trigger Not Putting Data in History Table
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
SE开发者_JS百科T 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.
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?
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.
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
精彩评论