我在一个表中有触发器,并且想要在插入,更新或删除行时读取UserId值.怎么做？下面的代码不起作用,我在UPDATED上收到错误
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;
DECLARE
@UserId INT,SELECT @UserId = INSERTED.UserId FROM INSERTED,DELETED
UPDATE dbo.[User] SET CreditsLeft = CreditsLeft - 1 WHERE Id = @UserId
END
解决方法
请注意,插入,删除意味着删除插入的CROSS JOIN相同的东西,并给出每行的每个组合.我怀疑这是你想要的.
这样的事情可能会帮助你开始…
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或deleted.userID等引用您感兴趣的表.
最后,请注意,插入和删除是表,并且可以(并且做)包含多个记录.
如果您一次插入10条记录,则插入的表将包含所有10条记录.这同样适用于删除和删除的表.并且在更新的情况下两个表.
编辑示例OPs编辑后触发.
ALTER TRIGGER [dbo].[UpdateUserCreditsLeft]
ON [dbo].[Order]
AFTER INSERT,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的PrimaryKey与UserID不同,请在FULL OUTER JOIN中使用.
