SQL Server：触发如何读取插入,更新,删除的值

发布时间：2020-12-12 16:44:32 所属栏目：MsSql教程来源：网络整理

导读：我在一个表中有触发器,并且想要在插入,更新或删除行时读取UserId值.怎么做？下面的代码不起作用,我在UPDATED上收到错误 ALTER TRIGGER [dbo].[UpdateUserCreditsLeft] ON [dbo].[Order] AFTER INSERT,UPDATE,DELETEAS BEGIN -- SET NOCOUNT ON added to preve

我在一个表中有触发器,并且想要在插入,更新或删除行时读取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中使用.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!