Having trouble getting it to work.

CREATE TRIGGER photo_numbers

ON mls_photo

FOR INSERT , UPDATE

as

declare @PhotoState varchar(20)

set @PhotoState = case when inserted.medium_photo is null then NULL else '1' end

+ case...

That is awesome Paul! Thank you for all the help. And for nigelrivett's help as well.

I didn't get any response at the Microsoft SQL forum. I think I'll stick...

Thanks Paul, that does work fine.

I was wondering though, if it is possible to put this calculated field into the mls table and reference the mls_photo table from there?...

Here are the table defs I inherited. The mls table is truncated down to 3 fields. I did not add the calculated field of course to the mls table yet....

Paul,

Dont get me wrong. I thought your solution was great - intricate but great.

alter table mls add [photoc] AS (

isnull(nullif('1',isnull([vreb_photo].[mls_photo].[medium_photo],'1')),'') +

isnull(nullif('2',isnull([vreb_photo].[mls_photo].[medium_photo2],'2')),'') +

isnull(nullif('3',isnull([vreb_photo].[mls_photo].[medium_photo3],'3')),'') +

isnull(nullif('4',isnull([vreb_photo].[mls_photo].[medium_photo4],'4')),'') +

isnull(nullif('5',isnull([vreb_photo].[mls_photo].[medium_photo5],'5')),'') +

isnull(nullif('6',isnull([vreb_photo].[mls_photo].[medium_photo6],'6')),'') +

isnull(nullif('7',isnull([vreb_photo].[mls_photo].[medium_photo7],'7')),'') +

isnull(nullif('8',isnull([vreb_photo].[mls_photo].[medium_photo8],'8')),'') +

isnull(nullif('9',isnull([vreb_photo].[mls_photo].[medium_photo9],'9')),''))

I get...

vreb_photo is the database,

mls_photo is the table,

medium_photo is the column.

The 2 tables have an [id] column in common.

Could I get some help on a trigger? This is my first attempt...

Hi nigelrivett,

I used your case statement and created a computed field in the mls_photo table. It works great.

But I would like to add a calc field to my mls table....

Hi Paul,

That's absolutely disgusting. It'll take me hours just to figure out! I love functions like this one! Hope your brain stops hurting soon...

That will be great for fixing the...

I am not sure how I would use this. Could you give me an example please?

Also, any good sources to find out about the object stream?

Thank you Andy