从插入和删除获取更新的数据
我有物理表持有标识,以及插入,删除,更新数据的时间 并有我的触发器附加(插入,更新和删除后)与基表。它是一种跟踪。从插入和删除获取更新的数据
插入的记录和删除的记录都很好,但问题是我没有在基表中获取更新记录的任何记录! 记录在插入和删除两个。
如果我在这里缺少任何东西,请帮助我。
触发插入和更新操作:
CREATE TRIGGER [dbo].[trArticleAttachmentTrack]
ON [dbo].[ArticleAttachment]
AFTER INSERT, UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
SET NOCOUNT ON;
INSERT INTO TrackArticleAttachmentTbl (ArticleAttachmentID, ActionOn)
SELECT i.ArticleAttachmentID, GETDATE() FROM inserted i
END
GO
触发了删除操作:
CREATE TRIGGER [dbo].[trArticleAttachmentTrackDelete]
ON [dbo].[ArticleAttachment]
AFTER DELETE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
SET NOCOUNT ON;
INSERT INTO TrackArticleAttachmentTblRowDel (ArticleAttachmentID, ActionOn)
SELECT i.ArticleAttachmentID, GETDATE() FROM deleted i
END
GO
查询来获取输出
SELECT
CASE WHEN i.ArticleAttachmentID IS NULL THEN 'This is a delete'
WHEN d.ArticleAttachmentID IS NULL THEN 'This is an insert'
ELSE 'This is an update'
END as Action,
*
FROM
TrackArticleAttachmentTbl i
FULL OUTER JOIN
TrackArticleAttachmentTblRowDel d
ON i.ArticleAttachmentID = d.ArticleAttachmentID
OUTPUT:
This is an update 38 18226 2012-02-19 14:01:52 5 18226 2012-02-19 14:02:01
This is an update 22 18210 2012-02-15 16:38:39 4 18210 2012-02-15 16:39:29
This is an update 13 18201 2012-02-15 12:21:22 2 18201 2012-02-15 12:21:32
This is an update 47 18235 2012-02-19 16:37:54 6 18235 2012-02-19 17:09:53
查询来获取更新的记录
SELECT *
FROM dbo.ArticleAttachment
WHERE ArticleAttachmentID IN (18226,18210,18201,18235)
没有结果这个:(
CASE表达式无法正确检测删除。试试这个。
CASE WHEN d.ArticleAttachmentID IS NOT NULL
THEN 'This is a delete'
ELSE 'This is an insert or update'
END
我用case表达只是为了澄清事物并确定更新,但是(更新的记录)加入插入和删除,它们不在基表中。 :( – 2012-02-29 08:32:28
如果trArticleAttachmentTrackDelete中存在一个ID,那么它将被删除。它不会存在于ArticleAttachment表中。 – 2012-02-29 08:42:50
将UPDATE视为DELETE/INSERT组合 – 2012-02-29 09:00:10
我们如何在没有看到您的代码的情况下为您提供帮助?请发布您的触发代码。 – sisdog 2012-02-29 07:08:20
如果您提供了触发器的脚本,我们可以帮助您。 – 2012-02-29 07:08:44
我已经提供了触发器和查询输出的代码 – 2012-02-29 07:46:14