SQL Server：触发如何读取Insert，Update，Delete的值

醉花荫眼儿媚

我在一个表中有触发器，并且想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
                     WHEN  deleted.UserID IS NULL THEN
                                                ELSE
               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
如果的PrimaryKeyUpdateUserCreditsLeft不是UserID，则在FULL OUTER JOIN中使用它。

SQL Server：触发如何读取Insert，Update，Delete的值

醉花荫眼儿媚 LV1