1

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.

asked Nov 21, 2014 at 2:31
0

2 Answers 2

4

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

vijayp
2,7861 gold badge18 silver badges20 bronze badges
answered Nov 21, 2014 at 10:58
0
6

Try this

create trigger utr_ValInfo on Table1
after insert as
begin
 declare @size int = 4;
 UPDATE Table1 
 SET 
 Tab1_valInfo = right('0000' + convert(varchar, i.Tab1_val1), @size) 
 + '-' +
 right('0000' + convert(varchar, i.Tab1_val2), @size)
 FROM Inserted i
 WHERE 
 Table1.Tab1_id= i.Tab1_id;
end;

and don't declare varchar in convert without length

Aaron Bertrand
182k28 gold badges406 silver badges625 bronze badges
answered Nov 21, 2014 at 3:39
0

Your Answer

Draft saved
Draft discarded

Sign up or log in

Sign up using Google
Sign up using Email and Password

Post as a guest

Required, but never shown

Post as a guest

Required, but never shown

By clicking "Post Your Answer", you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.