CODEWITHSUNDEEP
sqlsql-server
Kirs Sudh
Kirs Sudh

Reputation: 298

Updating a column when a new row is inserted in sql server

This trigger is creating problem, i could not find out why. 

CREATE TRIGGER initialpay 
   ON  table1
   AFTER INSERT
AS 
BEGIN
SET NOCOUNT ON;
declare @value char
select @value = '1000000' from inserted

    update table1 
    set table1.column1 = @value

END

When the new row is inserted, the whole column of all the rows is getting updated. I want to update only the newly inserted row. Can anyone tell me what is the prob?

Upvotes: 0

Views: 2620

Answers (2)

Vaibhav Parmar
Vaibhav Parmar

Reputation: 643

you have to use like the following syntax:

 update table1 
 set table1.column1 = @value
 FROM inserted
 WHERE table1.id= inserted.id;

Upvotes: 0

rudi bruchez
rudi bruchez

Reputation: 642

You are assuming that there is only one row inserted. You need to make your trigger set-oriented, otherwise you will miss multiline insertions.

CREATE TRIGGER initialpay 
   ON  table1
   AFTER INSERT
AS BEGIN
    IF @@ROWCOUNT = 0 RETURN
    SET NOCOUNT ON;

    update t1
    set column1 = i.col
    from table1 t1
    join inserted i on t1.id = i.id

END

Upvotes: 4

Related Questions