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
:
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)
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);
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:
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
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#"?!