在SQL Server中使用触发器 [英] using triggers in SQL Server

问题描述

您好，我一直在尝试尝试执行此触发器，但无法成功执行.一旦用户解除阻止，该程序应将登录尝试次数设为0.任何帮助将不胜感激.

Hi, I have been trying to attempt this trigger but couldn''t manage to do so. The program is supposed to turn the login attempts to 0 once the user is unblocked. Any help would be appreciated.

CREATE TRIGGER UserTrigger13
   ON  Users
   After update 
AS 
DECLARE @Username nvarchar(50)
DECLARE @Num int


SET @Username = (SELECT Username FROM updated)
SET @Num = 0

UPDATE Users SET UserLoginAttempts = @Num WHERE UserBlocked = 0 AND Users.Username = @Username

BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for trigger here

END
GO

推荐答案

您的begin..end块似乎有些奇怪.应该是

Your begin..end block seems a bit odd. Should it be

CREATE TRIGGER UserTrigger13
   ON  Users
   After update 
AS 
  DECLARE @Username nvarchar(50);
  DECLARE @Num int;
BEGIN
  SET @Username = (SELECT Username FROM inserted);
  SET @Num = 0;
 
  UPDATE Users SET UserLoginAttempts = @Num 
  WHERE UserBlocked = 0 AND Users.Username = @Username;
END
GO

如果update语句不更新任何行，则可能是原因所在.

例如，检查触发触发器时被阻止的用户确实为0.

您还可以使用打印 [

If the update statement isn''t updating any rows, then the probable cause is in the conditions.

Check for example that the user blocked is really 0 when the trigger fire.

You can also use PRINT[^] to output the variables etc to get more information about the execution.

这篇关于在SQL Server中使用触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！