我在一个表中有触发器,并且想在插入,更新或删除行时读取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
最佳答案
请注意,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
或deleted.userID
等引用您感兴趣的表。最后,请注意
inserted
和deleted
是表,可以(并且确实)包含多个记录。如果您一次插入10条记录,则
inserted
表将包含所有10条记录。删除和deleted
表也是如此。并且两个表都在更新的情况下。编辑示例在OP编辑后触发。
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
的PrimaryKey不是UserID,请在FULL OUTER JOIN中使用它。关于sql - SQL Server:触发如何读取Insert,Update,Delete的值,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/8505924/