SQL Server 触发器更新列值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9641829/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server Trigger update column value
提问by LdB
i need an example of basic sql trigger, now i explain:
我需要一个基本的 sql 触发器示例，现在我解释一下：
I Have a table with 5 columns(column1, power, column3, column4, times)
我有一个有 5 列的表（列 1，功率，列 3，列 4，次）
the value of "power" change in real time (Are numbers), and his datatype is 'real' while the datatype of "times" is 'int'
“power”的值实时变化（是数字），他的数据类型是“real”，而“times”的数据类型是“int”
i would a trigger that everytime "power" go to 0 'times' increase by 1
我会触发每次“功率”变为 0“次”增加 1
sorry if my english isn't perfect! and hope you understood what i mean!if something isn't clear tell me and i will try to explain better! :)
对不起，如果我的英语不完美！希望你明白我的意思！如果有什么不清楚的告诉我，我会尽量解释得更好！:)
回答by Nikola Markovinovi?
A possible basic trigger:
一个可能的基本触发器：
create trigger MyBasicTrigger on MyBasicTable
after insert, update
as
-- Trigger rowcount should not mess with update that triggered it, search for it in documentation provided here
set NoCount ON
-- If power is mentioned in update/insert statement at all
if update(Power)
begin
-- Update times for each changed row that has value of power 0
-- Inserted table holds new values in updated rows
-- You didn't mention your PK column(s), so I assume the name would be "ID"
update MyBasicTable set Times = Times + 1
from MyBasicTable inner join inserted on MyBasicTable.ID = inserted.ID
where Inserted.Power = 0
end
Documentation for NoCount and update(power) is here.
NoCount 和 update(power) 的文档在这里。
回答by ron tornambe
Assuming column1 is the primary key, the general form of the trigger is as follows:
假设column1为主键，触发器的一般形式如下：
create trigger MyPower
on MyTable
after insert, update
as
if exists (select column1
from inserted i join MyTable m
on i.column1 = m.column1
and i.power = 0)
update MyTable set times = times + 1
where exists (select column1 from inserted i
join MyTable m
on i.column1 = m.column1)
