触发器不将数据放入历史表中
我有以下触发器（以及类似表上的其他触发器），有时无法将数据放入历史表中。它应该将数据完全按照插入/更新的方式放入历史表中，并标记日期。
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
架构：
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]
我一次仅在 productCustomFields 表中插入和更新一条记录。它似乎 99% 的时间都有效，并且很难测试是否失败。任何人都可以阐明我可能做错了什么或对于这种类型的触发器有更好的做法吗？
环境是Sql Server Express 2005。我还没有为这个特定的客户端推出sql server 的服务包。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我认为解决此问题的正确方法是在插入 dbo.historicProductCustomFields 表时保留 TRY CATCH 块并将错误写入自定义错误日志表。从那里很容易追踪到这一点。
我还在历史产品自定义字段表上看到了 PK，但是如果您在 ProductCustomFields 表中插入和更新给定记录，那么您不会在历史产品自定义字段表上遇到主键违规吗？
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?
您应该对要插入的表进行架构限定。
您应该检查以确保表上没有多个触发器，如果有的话，该类型的触发器只会触发 1 个触发器，如果定义了 2 个触发器，则它们会按随机顺序运行。换句话说，如果有 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.
尝试使用这个触发器。我只是给你举个例子，尝试用这个触发器编写触发器。
创建触发器 [dbo]。[insert_Assets_Tran]
开启 [dbo]。[AssetMaster]
插入后更新
开始
声明@isnum TINYINT；
SELECT @isnum = COUNT(*) FROM 插入；
如果（@isnum = 1）
插入资产交易
选择[AssetId]、[Brandname]、[SrNo]、[Modelno]、[Processor]、[Ram]、[Hdd]、[Display]、[Os]、[Office]、[Purchasedt]
,[Expirydt],[供应商],[VendorAMC],[类型名称],[位置名称],[Empid],[创建者],[创建时间],[修改者]
,[修改时间],[备注],[资产状态],[类别],[Oylstartdt],[Oylenddt],[配置]
,[AStatus],[Tassign]
FROM 插入;
其他
RAISERROR('未提供某些字段', 16, 1)
带有设置错误；
结尾
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