Trigger - Search and Update Problem

[abrewis]

I have a large table that has many datetime fields. These fields have been entered as 01/01/1900 as default rather than as nulls. I would like to write an update trigger for when the record is updated, to update each of the date fields to be a null should the date be 01/01/1900, otherwise it'll leave it as a valid date.

My question is, is there an easy was to scan an updated row for the date 01/01/1900 and then replace them with nulls without looking directly for the datetime fields in one go? Or is it just the case of writing a tigger with a series of IF statements for the datetime fields?

Many thanks in advance
abrewis

 

[DrSql]

First Update the date thats already in the table, then create a trigger to update.
UPDATE
update tablename set date =Null where Date='01/01/1900'
TRIGGER

CREATE
trigger Update_DATE_i on DATE for insert as
update tablename set date =Null where Date='01/01/1900'
END

Good Luck




Dr. Sql
goEdeveloper@yahoo.com
Good Luck.

 

[abrewis]

Thanks for your response DrSQL,
am I right in thinking my trigger will have to do a check on each date field in the table like below? I guess there is no other more efficient way of doing this?

CREATE
trigger Update_DATE_i on DATE for insert as

update tablename set date1 =Null where Date1='01/01/1900'
update tablename set date2 =Null where Date2='01/01/1900'
update tablename set date3 =Null where Date3='01/01/1900'
.
.
.
END

Thanks again for your help
abrewis

 

[DrSql]

Yes you are right. How many dates are you updating, monitor your performance for some time.
Take a backup before your intial update.
Good luck

Dr. Sql
goEdeveloper@yahoo.com
Good Luck.

 

[abrewis]

There are probably no more that 15 date fields, I shouldn't think it'll effect performance too much.

Many thanks for you help.
Alasdair