i need an example of basic sql trigger, now i explain:
I Have a table with 5 columns(column1, power, column3, column4, times)
the value of "power" change in real time (Are numbers), and his datatype is 'real' while the datatype of "times" is 'int'
i would a trigger that everytime "power" go to 0 'times' increase by 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! :)
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.
Assuming column1 is the primary key, the general form of the trigger is as follows:
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)
