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.
2 Answers 2
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
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;
Explore related questions
See similar questions with these tags.