Click here to Skip to main content
16,021,125 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
SQL
CREATE TRIGGER trg_update
ON dbo.registeruser
after update
AS

Begin

declare @user_id int,
declare @user_name varchar(50),
declare @password varchar(50),
declare @firstname varchar(50),
declare @lastname varchar(50),
declare @audit_Action varchar(100)


select @user_id int,
select @user_name varchar(50),
select @password varchar(50),
select @firstname varchar(50),
select @lastname varchar(50)

if update(user_id)
        set @audit_action='Updated Record -- After Update Trigger.';

    if update(user_name)
        set @audit_action='Updated Record -- After Update Trigger.';

        if update(password)
        set @audit_action='Updated Record -- After Update Trigger.';

        if update(firstname)
        set @audit_action='Updated Record -- After Update Trigger.';

        if update(lastname)
        set @audit_action='Updated Record -- After Update Trigger.';



        insert into old_registeruser (user_id,user_name,password,firstname,lastname) values (@user_id,@user_name,@password,@firstname,@lastname);


    PRINT 'AFTER UPDATE Trigger fired.'

End




i want to fire this trigger when update occurs. i have a stored procedure to update the records.


While i am saving this the Error is "Incorrect syntax near the keyword 'declare'" and "Must declare the scalar variable "user_id" "

this error is for all the fields that i have declared.
Posted

1 solution

First problem: Each DECLARE line is followed by a comma, indicating that the statement continues on the next line. Either remove the commas, replace them with semi-colons, or remove all but the first DECLARE:
SQL
declare @user_id int
declare @user_name varchar(50)
declare @password varchar(50)
declare @firstname varchar(50)
declare @lastname varchar(50)
declare @audit_Action varchar(100)

-- Or:
declare @user_id int;
declare @user_name varchar(50);
declare @password varchar(50);
declare @firstname varchar(50);
declare @lastname varchar(50);
declare @audit_Action varchar(100);

-- Or:
declare @user_id int,
        @user_name varchar(50),
        @password varchar(50),
        @firstname varchar(50),
        @lastname varchar(50),
        @audit_Action varchar(100)


Second problem: You've copied the DECLARE lines, and simply replaced the DECLARE keyword with SELECT. This is totally invalid syntax.

Third problem: Triggers can and do fire for multiple rows at the same time. It looks like your code is trying to process a single updated row, which isn't going to work.

Try something like this:
SQL
CREATE TRIGGER trg_update
ON dbo.registeruser
AFTER UPDATE
AS
BEGIN

declare @user_id int;
declare @user_name varchar(50);
declare @password varchar(50);
declare @firstname varchar(50);
declare @lastname varchar(50);
declare @audit_Action varchar(100);

    If update(user_id) Or update(user_name) Or update(password) Or update(firstname) Or update(lastname)
    BEGIN
        -- This doesn't seem to be used anywhere:
        -- SET @audit_action = 'Updated Record -- After Update Trigger.';

        INSERT INTO old_registeruser
        (
            user_id,
            user_name,
            password,
            firstname,
            lastname
        )
        SELECT
            user_id,
            user_name,
            password,
            firstname,
            lastname
        FROM
            deleted
        ;
    END;

    PRINT 'AFTER UPDATE Trigger fired.'
END



Other comments:

The fact that you have a password column suggests that you're storing passwords in plain text. That is a very bad idea - you should be storing a salted hash of the password.
Salted Password Hashing - Doing it Right[^]

Finally, why have you tagged this SQL question as "C#"?!
 
Share this answer
 
Comments
Member 9671810 14-Oct-14 12:12pm    
thanx a lot sir, i m beginner, so i don't had much idea about triggers. :)

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900