这是我写的一个创建触发器的代码(如下),要实现Stuff表中Position字段改变则Salary字段也跟着改变的功能
create trigger tri_test on Stuff
after update
referencing NEW AS table_after_update
OLD AS table_before_update
for each statement
begin
IF table_after_update.Position = 'Manager'
update Stuff set Salary = 5000
where StuffNum = table_after_update.stuffNum
else IF table_after_update.Position = 'Assistant'
update Stuff set Salary = 3000
where StuffNum = table_after_update.stuffNum
else IF table_after_update.Position = 'Supervisor'
update Stuff set Salary = 4000
where StuffNum = table_after_update.stuffNum
end运行时提示出错服务器: 消息 170,级别 15,状态 1,过程 tri_test,行 3
第 3 行: 'referencing' 附近有语法错误。我不知道问题出在哪里?请教各位,顺便问一下正确的应该怎么写?
create trigger tri_test on Stuff
after update
referencing NEW AS table_after_update
OLD AS table_before_update
for each statement
begin
IF table_after_update.Position = 'Manager'
update Stuff set Salary = 5000
where StuffNum = table_after_update.stuffNum
else IF table_after_update.Position = 'Assistant'
update Stuff set Salary = 3000
where StuffNum = table_after_update.stuffNum
else IF table_after_update.Position = 'Supervisor'
update Stuff set Salary = 4000
where StuffNum = table_after_update.stuffNum
end运行时提示出错服务器: 消息 170,级别 15,状态 1,过程 tri_test,行 3
第 3 行: 'referencing' 附近有语法错误。我不知道问题出在哪里?请教各位,顺便问一下正确的应该怎么写?
具体用法可以参考SQL Server的联机帮助。其实在SQL Server里面,inserted相当于你用到的New表,deleted相当于你用到的Old~
你做一下替换看看吧!(具体逻辑我没有看)
as
update Stuff set Salary = 5000 where Position = 'Manager'
update Stuff set Salary = 3000 where Position = 'Assistant'
update Stuff set Salary = 4000 where Position = 'Supervisor'
if update(Manager)
update Stuff set Salary = 5000
...