update field by using Triggers in MS SQL

Question

i have simple question regarding Triggers in sql.

I am completely new and i do not know how to handle it.

I have one table myshift with shiftid, starttime stoptime and lastupdate.

create trigger ShiftTriggerr on myshift for update as 
if update(stoptime)
update myshift set lastupdated = getdate()

what i want is when stoptime will update, the lastupdate field will update with getdate().

but when i run this it does not update one row but updated all rows. I do not know how to apply check on this trigger

Lamak · Accepted Answer · 2013-05-27 14:50:25Z

2

You need to use the INSERTED virtual table:

CREATE TRIGGER dbo.ShiftTriggerr 
ON dbo.myshift AFTER UPDATE
AS
BEGIN
     SET NOCOUNT ON;
     IF UPDATE(stoptime)
     BEGIN
         UPDATE A
         SET lastupdated = getdate() 
         FROM dbo.myshift A
         INNER JOIN INSERTED B
             ON A.shiftid = B.shiftid
     END
END

answered May 27, 2013 at 14:50

Lamak

70.8k12 gold badges119 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Charles Bretana · Accepted Answer · 2013-05-27 14:50:09Z

0

myshift should have a primary key defined, if that is shiftId, then,

  create trigger ShiftTriggerr on myshift for update as        
     update m
        set lastupdated = getdate() 
     From myshift m Join inserted i 
        on i.shiftId = m.shiftId

answered May 27, 2013 at 14:50

Charles Bretana

147k24 gold badges158 silver badges219 bronze badges

Collectives™ on Stack Overflow

update field by using Triggers in MS SQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related