创建触发器时出错 [英] Error while creating trigger
问题描述
CREATE TRIGGER trg_update
ON dbo.registeruser
update
AS
开始
声明 < span class =code-sdkkeyword> @ user_id int ,
声明 @ user_name varchar ( 50 ),
声明 @ password varchar ( 50 ),
声明 @ firstname varchar ( 50 ),
声明 @ lastname varchar ( 50 ),
声明 @ audit_Action varchar ( 100 )
select @ user_id int ,
select @ user_name varchar ( 50 ) ,
选择 @ password varchar ( 50 ),
选择 @ firstname varchar ( 50 ),
选择 @ lastname varchar ( 50 )
如果 update ( user_id)
set @ audit_action = ' 已更新记录 - 更新触发后。';
如果 update (user_name)
set @ audit_action = ' 更新记录 - 更新触发后。';
如果 update (密码)
set @ audit_action = ' 更新记录 - 更新触发后。';
如果 update (名字)
set @ audit_action = ' 更新记录 - 更新触发后。';
if update (lastname)
set @ audit_action = ' 更新记录 - 更新触发后。';
插入 进入 old_registeruser(user_id, user_name,password,firstname,lastname) values ( @ user_id , @user_name , @ password , @ firstname , @LastName 跨度>);
PRINT ' 更新触发后触发。
结束
i想要在更新发生时触发此触发器。我有一个存储过程来更新记录。
我保存时错误是关键字附近的语法不正确声明'和必须声明标量变量user_id
此错误适用于我声明的所有字段。
第一个问题:每个DECLARE
行后面跟一个逗号,表示该语句在下一行继续。删除逗号,用分号替换它们,或删除除第一个DECLARE
之外的所有内容:
< span class =code-keyword>声明 @ user_id int
< span class =code-keyword>声明 @ user_name varchar ( 50 )
声明 @ password varchar ( 50 )
声明 @ firstname varchar ( 50 )
声明 @ lastname varchar ( 50 )
声明@ audit_Action varchar ( 100 )
- 或者:
声明 @ user_id int ;
声明 @ user_name varchar ( 50 );
声明 @ password varchar ( 50 );
声明 @ firstname varchar ( 50 );
声明 @ lastname varchar ( 50 );
声明 @ audit_Action varchar ( 100 );
- 或者:
声明 @ user_id int ,
@ user_name varchar ( 50 ),
@ password varchar ( 50 ),
@ firstname varchar ( 50 ),
@ lastname varchar ( 50 ),
@ audit_Action varchar ( 100 )
第二个问题:您已经复制了DE CLARE
行,只需用SELECT
替换DECLARE
关键字。这是完全无效的语法。
第三个问题:触发器可以同时触发多个行时间。看起来您的代码正在尝试处理单个更新的行,这不会起作用。
尝试这样的事情:
CREATE TRIGGER trg_update
ON dbo.registeruser
AFTER UPDATE
AS
BEGIN
声明 @ user_id int ;
声明 @ user_name varchar ( 50 );
声明 @ password varchar ( 50 );
声明 @ firstname varchar ( 50 );
声明 @ lastname varchar ( 50 );
声明 @ audit_Action varchar ( 100 );
如果 update (user_id)或 update (user_name)或 update (密码)或 更新(名字)或者 更新(姓氏)
BEGIN
- 这似乎没有在任何地方使用:
- SET @audit_action ='更新记录 - 更新后触发。';
INSERT INTO old_registeruser
(
user_id,
user_name,
密码,
firstname,
lastname
)
SELECT
user_id,
user_name,
密码,
firstname,
lastname
FROM
删除
;
END ;
PRINT ' 更新后触发器触发。'
END
其他评论:
你有密码的事实
列表明您以纯文本格式存储密码。这是一个非常糟糕的主意 - 你应该存储密码的盐渍哈希。
Salted Password Hashing - 正确行事 [ ^ ]
最后,为什么要将这个SQL问题标记为C#?!
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.
First problem: EachDECLARE
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 firstDECLARE
:
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 theDECLARE
lines, and simply replaced theDECLARE
keyword withSELECT
. 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 -- 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 apassword
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#"?!
这篇关于创建触发器时出错的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!