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
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.