MS SQL Server - trigger - crate string from other columns

Author

trigger - crate string from other columns

adamwalan@yahoo.com

2006-10-24, 6:37 pm

I have table table1 with columns pk - int, is_blond - bit , is_brunet -
bit , test - char(200)

I want to create trigger, which will do:

if is_blond=1
temp="one"
ELSE
temp="zero"
if is_brunet=1
temp=temp+" three"
ELSE
temp=temp+" four"

UPDATE table1 set test=temp WHERE pk=pk

Any ideas how to do this ?

Uri Dimant

2006-10-24, 6:37 pm

I'm afraid did not usnderstand tyour request
if it does not help , please provide DDL+ sample data

create table t (is_blond int , is_brunet int, test varchar(50))

---having some data
insert into t (is_blond,is_brunet)
values (1,110)
insert into t (is_blond,is_brunet)
values (2,80)
insert into t (is_blond,is_brunet)
values (1,2)
insert into t (is_blond,is_brunet)
values (5,1)
insert into t (is_blond,is_brunet)
values (1,8)
insert into t (is_blond,is_brunet)
values (6,1)

declare @t varchar(50)
set @t=''
update t set @t=test=case when is_blond =1 then 'one'
when is_brunet= 1 then 'three'
else 'zero' end
where is_blond=2

select * from t

drop table t

<adamwalan@yahoo.com> wrote in message
news:1160630662.691193.44550@m73g2000cwd.googlegroups.com...
>I have table table1 with columns pk - int, is_blond - bit , is_brunet -
> bit , test - char(200)
>
> I want to create trigger, which will do:
>
> if is_blond=1
> temp="one"
> ELSE
> temp="zero"
> if is_brunet=1
> temp=temp+" three"
> ELSE
> temp=temp+" four"
>
> UPDATE table1 set test=temp WHERE pk=pk
>
> Any ideas how to do this ?
>

Paul Ibison

2006-10-24, 6:37 pm

Adam,

I can rewrite this as TSQL to make it trigger enabled but I'd like to check
the logic first. Is it intended that you'll have strings like 'one three',
'zero four' etc? Surely the check for brunette only applies once the
is_blond has failed? I have listed what I would expect to exist below:

blond not brunette 'one'
not blond brunette 'two'
not blond not brunette 'zero'
blond brunette - makes no sense?

Note that there is no 'four' and no prefixing spaces for concatenation of
strings.

Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Hilary Cotter

2006-10-24, 6:37 pm

To steal Uri's data:

create table t (pk int not null identity primary key, is_blond int ,
is_brunet int, test varchar(50))
GO
insert into t (is_blond,is_brunet)
values (1,110)
insert into t (is_blond,is_brunet)
values (2,80)
insert into t (is_blond,is_brunet)
values (1,2)
insert into t (is_blond,is_brunet)
values (5,1)
insert into t (is_blond,is_brunet)
values (1,8)
insert into t (is_blond,is_brunet)
values (6,1)
GO
alter trigger t_trigger on t for update
as
set ansi_nulls off
update t set t.test=case
when inserted.is_blond =1 then 'one'
when inserted.is_blond <>1 then 'zero' end
from t, inserted
where t.pk=inserted.pk
update t set t.test=case
when inserted.is_brunet =1 then coalesce(t.test,'')+' three'
when inserted.is_brunet <>1 then coalesce(t.test,'')+' four'
end
from t, inserted
where t.pk=inserted.pk
GO

select * from t
update t set is_blond=1 where pk=1

the problem is that you don't define what is that you don't define what
happens when someone is blond (ie blond =1) whether this update is mutually
exclusive with the is_brunet property.
drop table t

--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

<adamwalan@yahoo.com> wrote in message
news:1160630662.691193.44550@m73g2000cwd.googlegroups.com...
>I have table table1 with columns pk - int, is_blond - bit , is_brunet -
> bit , test - char(200)
>
> I want to create trigger, which will do:
>
> if is_blond=1
> temp="one"
> ELSE
> temp="zero"
> if is_brunet=1
> temp=temp+" three"
> ELSE
> temp=temp+" four"
>
> UPDATE table1 set test=temp WHERE pk=pk
>
> Any ideas how to do this ?
>

adamwalan@yahoo.com

2006-10-24, 6:37 pm

Uri Dimant wrote:
> I'm afraid did not usnderstand tyour request
> if it does not help , please provide DDL+ sample data

Yes, non of the posts anwered my question....(i will change example)
Basically table contain answers to question "Have you ever travel to"
The table (table1) looks like this:

pk(int) London(bit) Chicago(bit) Moscow(bit)
Answer(chr[254])
1 0 1 1
NULL
2 1 1 0
NULL
3 0 0 0
NULL

I want trigger which will update column answer, so after trigger fired
it will look like:
pk(int) London(bit) Chicago(bit) Moscow(bit)
Answer(chr[254])
1 0 1 1
Chicago,London
2 1 1 0
London,Moscow
3 0 0 0

Thanks,
Adii

Uri Dimant

2006-10-24, 6:37 pm

Again, please post DDL+ sample data

Something like this ---taken from Aaron's web site

CREATE TABLE dbo.Stores
(
StoreID INT IDENTITY(1,1)
PRIMARY KEY,
Storename VARCHAR(32) NOT NULL,
Zip CHAR(5) NOT NULL
)
GO

SET NOCOUNT ON
INSERT Stores(Address, Zip) SELECT 'Adelphia Fireworks ', '07710'
INSERT Stores(Address, Zip) SELECT 'West Milford BottleRockets', '07480'
INSERT Stores(Address, Zip) SELECT 'Explosives of Ogdensburg ', '07439'
INSERT Stores(Address, Zip) SELECT 'Pompton Cherry Bombs, Inc.', '07442'

<adamwalan@yahoo.com> wrote in message
news:1160714209.424798.98740@m73g2000cwd.googlegroups.com...
> Uri Dimant wrote:
>
> Yes, non of the posts anwered my question....(i will change example)
> Basically table contain answers to question "Have you ever travel to"
> The table (table1) looks like this:
>
> pk(int) London(bit) Chicago(bit) Moscow(bit)
> Answer(chr[254])
> 1 0 1 1
> NULL
> 2 1 1 0
> NULL
> 3 0 0 0
> NULL
>
>
> I want trigger which will update column answer, so after trigger fired
> it will look like:
> pk(int) London(bit) Chicago(bit) Moscow(bit)
> Answer(chr[254])
> 1 0 1 1
> Chicago,London
> 2 1 1 0
> London,Moscow
> 3 0 0 0
>
>
> Thanks,
> Adii
>

Hugo Kornelis

2006-10-24, 6:37 pm

On 11 Oct 2006 22:24:22 -0700, adamwalan@yahoo.com wrote:

>I have table table1 with columns pk - int, is_blond - bit , is_brunet -
>bit , test - char(200)
>
>I want to create trigger, which will do:
>
>if is_blond=1
>temp="one"
>ELSE
>temp="zero"
>if is_brunet=1
>temp=temp+" three"
>ELSE
>temp=temp+" four"
>
>UPDATE table1 set test=temp WHERE pk=pk
>
>Any ideas how to do this ?

Hi adamwalan,

Don't use a trigger at all. Create a computed column:

CREATE TABLE table1
(-- other columns,
is_blond tinyint NOT NULL,
is_brunet tinyint NOT NULL,
test AS CASE WHEN is_blond = 1 THEN 'one' ELSE 'zero' END
+ CASE WHEN is_brunet = 1 THEN ' three' ELSE ' four' END
);

--
Hugo Kornelis, SQL Server MVP

Sponsored Links