SQL Server：触发如何读取Insert，Update，Delete的值 [英] SQL Server : trigger how to read value for Insert, Update, Delete

问题描述

我在一个表中有触发器，并且想在插入，更新或删除行时读取 UserId 值。怎么做？下面的代码不起作用，我在 UPDATED

I have the trigger in one table and would like to read UserId value when a row is inserted, updated or deleted. How to do that? The code below does not work, I get error on 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

推荐答案

请注意，插入，删除的含义与插入的交叉连接删除相同，并给出每种组合每行。我怀疑这就是您想要的。

Please note that inserted, deleted means the same thing as inserted CROSS JOIN deleted and gives every combination of every row. I doubt this is what you want.

像这样的事情可能有助于您入门...

Something like this may help get you started...

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 等。

Depending on what you want to do, you then reference the table you are interested in with inserted.userID or deleted.userID, etc.

最后，请注意已插入 和删除的是表，并且可以（并且确实）包含多个记录。

Finally, be aware that inserted and deleted are tables and can (and do) contain more than one record.

如果一次插入10条记录，则插入的表将包含所有10条记录。删除和已删除表也是如此。以及两个表都进行更新。

If you insert 10 records at once, the inserted table will contain ALL 10 records. The same applies to deletes and the deleted table. And both tables in the case of an update.

编辑示例在OP编辑后触发。

EDIT Examplee Trigger after OPs edit.

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中使用。

If the PrimaryKey of UpdateUserCreditsLeft is something other than UserID, use that in the FULL OUTER JOIN instead.

这篇关于SQL Server：触发如何读取Insert，Update，Delete的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！