CODEWITHSUNDEEP
sqlsql-servertriggers
LdB
LdB

Reputation: 101

SQL Server Trigger update column value

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! :)

Upvotes: 2

Views: 28334

Answers (2)

ron tornambe
ron tornambe

Reputation: 10780

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)

Upvotes: 2

Nikola Markovinović
Nikola Markovinović

Reputation: 19346

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.

Upvotes: 6

Related Questions