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.

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

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

0

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.