请注意inserted, deleted
意思是一样的inserted CROSS JOIN deleted
并给出每一行的每种组合。我怀疑这就是你想要的。
像这样的事情可能会帮助你开始......
SELECT
CASE WHEN inserted.primaryKey IS NULL THEN 'This is a delete'
WHEN deleted.primaryKey IS NULL THEN 'This is an insert'
ELSE 'This is an update'
END as Action,
*
FROM
inserted
FULL OUTER JOIN
deleted
ON inserted.primaryKey = deleted.primaryKey
根据您想要执行的操作,然后引用您感兴趣的表inserted.userID
or deleted.userID
, etc.
最后,请注意inserted
and deleted
are tables并且可以(并且确实)包含多个记录。
如果一次插入10条记录,inserted
表将包含全部 10 条记录。这同样适用于删除和deleted
桌子。并且两个表都在更新的情况下。
EDITOP 编辑后触发示例。
ALTER TRIGGER [dbo].[UpdateUserCreditsLeft]
ON [dbo].[Order]
AFTER INSERT,UPDATE,DELETE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
UPDATE
User
SET
CreditsLeft = CASE WHEN inserted.UserID IS NULL THEN <new value for a DELETE>
WHEN deleted.UserID IS NULL THEN <new value for an INSERT>
ELSE <new value for an UPDATE>
END
FROM
User
INNER JOIN
(
inserted
FULL OUTER JOIN
deleted
ON inserted.UserID = deleted.UserID -- This assumes UserID is the PK on UpdateUserCreditsLeft
)
ON User.UserID = COALESCE(inserted.UserID, deleted.UserID)
END
如果主键为UpdateUserCreditsLeft
是 UserID 以外的其他内容,请在 FULL OUTER JOIN 中使用它。