TRIGGER TO UPDATE TABLE FROM IT OWN VALUES

[kp1279]

I hope some of this makes some sense to you?

I have a number of tables, all with the same problem.

one of my columns is a concatenation of 3 other columns, how can I create a trigger that will update this column automatically when the records have been created?

The columns in question are:

type_id, varchar(2)
urn, int
year_id, char(4)
set_id, char(10)

set_id is a concatenation of the other 3, this code works fine if ran on its own:

update set_records set set_id=type_id + '/' + (cast(urn as varchar(10))) + '/' + year_id

This brings back the values as I want them i.e. RR/1/2005

How can I create this as a update trigger in the table set_records??

 

[lpatnaik]

I have cited below an example on doing the same. You can modify the example to suit your condition.
This trigger trgConcat is used on a table Test which has 4 columns namely Col1, col2, col3, col4 all of varchar type. The trigger updates the column col4 with the concatenated string value from col1, col2 and col3 of Test.

CREATE TRIGGER trgConcat ON [dbo].[Test]
FOR INSERT
AS
begin

declare @s1 varchar(100),
@s2 varchar(100),
@s3 varchar(100)


select @s1 = col1, @s2 = col2, @s3 = col3 from inserted

update Test set col4 = @s1 + @s2 + @s3
where col1 = @s1

end

Create the above trigger on table Test and run the following insert statement and check the result:
insert into Test(col1,col2,col3) values('A','B','C')

 

[kp1279]

Thanks for this code, however there are still problems:

I have changed the code to suit:

CREATE TRIGGER trgConcat ON set_records
FOR INSERT
AS
begin

declare @s1 varchar(10),
@s2 varchar(10),
@s3 varchar(10)


select @s1 = type_id, @s2 = URN, @s3 = year_id from inserted

update Test set set_id = @s1 + @s2 + @s3
where type_id = @s1

end

This when adding a new record brings back an error message ststing the subquery is returning more than 1 value.

where is the problem??

Is it that I am trying to concatenate differing types, i.e. char, bit and varchar etc?

As stated the original code worked fine if ran alone, but can't figure out how to build it into the code.

The reason for the concatenation is that I need 3 seperate bits to each records as a unique identifier, these are the record type, which is a two charactor type, i.e XX, then a unique URN which is incremental, then the year the record was created, i.e. 2005.

All these fields work fine on there own, but not when concatenated.

any idea's

thanks

 

[ChrisCarroll]

Is there a reason for trying to to this with a trigger rather than a calculated column?

It does seem to me that a calculated column is designed to to exactly what you want without the hassle of a trigger.

What you really want is

Drop Trigger trgConcat

Alter Table temp_table
  Drop Column set_id
Alter Table temp_table
  Add set_id As type_id + '/' + 
                cast(urn as varchar(10)) + '/' +
                 year_id

(The problem with that your select statement in lpatnaik's trigger is that you are assigning values to variables @s1 etc.
A variable can have only one value. Therefore, a select statement assigning to a variable must return only 1 row.

So your trigger fails when you insert more than one row.
An alternative would be

CREATE TRIGGER trgConcat ON set_records
FOR INSERT
AS
Begin
Update Test 
    Set Set_id = Inserted.type_id + 
                 Cast( Inserted.URN as Varchar(10)) +
                 Inserted.year_id 
    From Inserted
End

 

[kp1279]

Many many thanks

I am new to this, I have only been creating a database for two months, after 3 days tuition, so all your help is apreciated.

I went with option 1, and used the caluculated column, which worked fine, what I did then for the other tables is went into the table design, and copied the formula from the field to the other tables, with slight ammendments relative to the other tables, and this worked perfectly.

again many thanks.

KP