从INSERT触发器更新时触发更新后未触发
问题描述:
我有一个表中的两个触发器,在插入一个火灾和从INSERT触发器更新时触发更新后未触发
CREATE TRIGGER [dbo].[eTteamTg]
ON [dbo].[entryTable]
AFTER INSERT
AS
BEGIN
UPDATE entryTable
SET shiftTeam = (SELECT TOP 1 shiftTeamMemb.teamId
FROM shiftTeamMemb
WHERE shiftTeamMemb.personalNumber = i.personalNumber)
FROM entryTable
INNER JOIN inserted i ON i.ID = entryTable.ID
END
而第二个触发更新
后与来自其他表的其他信息更新该行CREATE TRIGGER [dbo].[eThistoryUpdTg] ON [dbo].[entryTable]
AFTER update
AS
BEGIN
INSERT INTO eThistory(my columns)
SELECT
*, HOST_Name() + ' ' + SUSER_NAME() + ' Upd', GETDATE()
FROM
deleted
END
我需要做的是不火AFTER UPDATE
触发,当我与AFTER INSERT
触发更新行。可能吗 ?
答
我建议你到这两个触发器合并为一个:
CREATE TRIGGER [dbo].[eTteamTg]
ON [dbo].[entryTable]
AFTER INSERT, UPDATE
AS
BEGIN
UPDATE entryTable
SET shiftTeam = (SELECT TOP 1 shiftTeamMemb.teamId
FROM shiftTeamMemb
WHERE shiftTeamMemb.personalNumber = i.personalNumber)
FROM entryTable
INNER JOIN inserted i ON i.ID = entryTable.ID
INSERT INTO eThistory(my columns)
SELECT
*, HOST_Name() + ' ' + SUSER_NAME() + ' Upd', GETDATE()
FROM
deleted
END
然后插入后,它将更新shiftTeam
和'deleted1将不会被使用。
更新后,它将更新shiftTeam
并使用deleted
获取以前(甚至更高版本)值。
或者,如果你不需要在此表上的UPDATE的情况下更新shiftTeam
您可以添加IF语句:
CREATE TRIGGER [dbo].[eTteamTg]
ON [dbo].[entryTable]
AFTER INSERT, UPDATE
AS
BEGIN
IF (SELECT COUNT(*) FROM deleted) = 0 --That means it was INSERT
BEGIN
UPDATE entryTable
SET shiftTeam = (SELECT TOP 1 shiftTeamMemb.teamId
FROM shiftTeamMemb
WHERE shiftTeamMemb.personalNumber = i.personalNumber)
FROM entryTable
INNER JOIN inserted i ON i.ID = entryTable.ID
END
INSERT INTO eThistory(my columns)
SELECT
*, HOST_Name() + ' ' + SUSER_NAME() + ' Upd', GETDATE()
FROM
deleted
END
+0
太棒了!这正是我所需要的,我不知道你可以在更多的行动上做同样的触发。这种方式非常聪明。我必须使用第二种方法,因为我只需要在插入时更新它 –
答
实现你的第二个触发这个样子,
CREATE TRIGGER [dbo].[eThistoryUpdTg] ON [dbo].[entryTable]
AFTER UPDATE
AS
BEGIN
INSERT INTO eThistory (my columns)
SELECT *
,HOST_Name() + ' ' + SUSER_NAME() + ' Upd'
,GETDATE()
FROM deleted d
WHERE NOT EXISTS (
SELECT TOP 1 shiftTeamMemb.teamId
FROM shiftTeamMemb
INNER JOIN inserted I ON i.personalNumber = shiftTeamMemb.personalNumber
INNER JOIN entryTable E ON E.ID = I.ID
WHERE shiftTeamMemb.personalNumber = i.personalNumber
)
END
你'插入'触发似乎被打破。这个子查询是不相关的(它引入了一个单独的引用来插入''而不是我猜你正在尝试做什么,参考'i'),所以多行插入将产生一个错误,沿着“子查询返回更多比一个价值“。 –
哦,我现在看到了,更新了这个问题@Damien_The_Unbeliever –
对不起,我错了。它不会返回该错误。相反,它会默默地破坏你的数据,因为你已经用'top 1'强制它。再次,这个子查询是*不相关*。因此,如果'inserted'包含* multiple * rows与* different *'personalNumber'值,那么*这些值中的一个将成功地在'shiftTeamMemb'中发生查找,然后*每个原来插入的行将接收该值。 –