SQL Server 2014 – How to Update a Column with Values from the Same Newly Inserted Row in a Trigger

sql serversql server 2014triggerupdate

Using SQL Server 2014, I have this:

Table1
Tab1_id        int identity(1,1) primary key
Tab1_val1      smallint
Tab1_val2      smallint
Tab1_valInfo   varchar(10) -- not yet implemented, will be used only for presentation

 Tab1_id |   Tab1_val1 |   Tab1_val2 | Tab1_valInfo
---------|-------------|-------------|--------------
       1 |          25 |          19 | 0025-0019
       2 |           0 |           5 | 0000-0005
       3 |          12 |           3 | 0012-0003

Now with my pretty short SQL experience, what I'm trying is:

From an application where the only input values required for inserting data are Tab1_val1 and Tab1_val2, then, create a trigger which updates Tab1_valInfo column.

create trigger utr_ValInfo on Table1
after insert as
begin

    declare @size int = 4;

    declare @valInfo varchar(10) =
        select right('0000' + convert(varchar, /* insertedRow.Tab1_val1 */), @size) from inserted
        + '-' +
        select right('0000' + convert(varchar, /* insertedRow.Tab1_val2 */), @size) from inserted;

    update Table1 set Tab1_valInfo = @valInfo where /* this is the last inserted row*/;

end;

A single insert may involve multiple rows.

I hope what I'm trying to achieve is clear.

Best Answer

You can try Computed column rather than triggers, since its easy to modify . Try below query 

create table tab_computed 
(
Tab1_id        int identity(1,1) primary key,

Tab1_val1      smallint,

Tab1_val2      smallint,

Tab1_valInfo   as (right('0000' + convert(varchar,Tab1_val1), 4)+ '-' + right('0000' + convert(varchar,Tab1_val2), 4)) 
 ) 

insert into tab_computed 
(Tab1_val1,Tab1_val2) values (25,19),(0,5),(12,3)

select * from tab_computed

Result set :

enter image description here

Related Question