using triggers in SQL Server

Question

0.00/5 (No votes)

See more:

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.

SQL

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

Posted 16-May-12 23:58pm

kim061

Updated 17-May-12 0:05am

Wendelius

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2012-05-17T00:11:00

Solution 1

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

SQL

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.

Posted 17-May-12 0:11am

Wendelius

Updated 17-May-12 2:01am

v2

Comments

kim061 17-May-12 6:33am

I tried this and didn't work. I tried the tutorial and didn't find it to suit my needs

Wendelius 17-May-12 7:13am

When you say it didn't work, what do you mean, did you get an error (if you did, what is it) or was no rows updated or something else?

kim061 17-May-12 7:14am

I had a custom error made with a method and it stated that row cannot be updated

Wendelius 17-May-12 7:16am

Ok, so what was the error you received from database. Pleas post also the code where the error occurred.

kim061 17-May-12 8:23am

it wasn't an error just a predefined msg

Wendelius 17-May-12 8:36am

Ok, and you have used inserted in the trigger:

SET @Username = (SELECT Username FROM inserted);