我在一个表中有触发器,并且想在插入,更新或删除行时读取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, deletedinserted 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.userIDdeleted.userID等引用您感兴趣的表。

最后,请注意inserteddeleted是表,可以(并且确实)包含多个记录。

如果您一次插入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/

10-13 05:24