Consolidate Trigger 1

[ousoonerjoe]

Due to legacy code, the date fields have to be set to NULL rather than use a default value (such as 1/1/1900). As a result, a trigger is used to insure that any default values are converted back to NULL.

Is there a more efficient approach to this?

ALTER TRIGGER [dbo].[trg_Tmpwpmas_DateNulls]
ON [dbo].[tmpwpmas]
FOR INSERT, UPDATE
AS

UPDATE Tmpwpmas
SET ModifiedBy = HOST_NAME(),
	ModifyDate = GETDATE()
FROM Tmpwpmas d
	INNER JOIN Inserted i on d.id_num = i.id_num;

UPDATE Tmpwpmas
SET wpmas_dt_billed = NULL
FROM Tmpwpmas d
	INNER JOIN Inserted i on d.id_num = i.id_num
WHERE i.wpmas_dt_billed = '1/1/1900';

UPDATE Tmpwpmas
SET wpmas_dt_complet = NULL
FROM Tmpwpmas d
	INNER JOIN Inserted i on d.id_num = i.id_num
WHERE i.wpmas_dt_complet = '1/1/1900';

UPDATE Tmpwpmas
SET wpmas_dt_pickup = NULL
FROM Tmpwpmas d
	INNER JOIN Inserted i on d.id_num = i.id_num
WHERE i.wpmas_dt_pickup = '1/1/1900';

UPDATE Tmpwpmas
SET wpmas_dt_schrtn = NULL
FROM Tmpwpmas d
	INNER JOIN Inserted i on d.id_num = i.id_num
WHERE i.wpmas_dt_schrtn = '1/1/1900';

UPDATE Tmpwpmas
SET wpmas_dt_shipped = NULL
FROM Tmpwpmas d
	INNER JOIN Inserted i on d.id_num = i.id_num
WHERE i.wpmas_dt_shipped = '1/1/1900';

UPDATE Tmpwpmas
SET wpmas_dt_started = NULL
FROM Tmpwpmas d
	INNER JOIN Inserted i on d.id_num = i.id_num
WHERE i.wpmas_dt_started = '1/1/1900';

--------------------------------------------------
Bluto: What? Over? Did you say "over"? Nothing is over until we decide it is! Was it over when the Germans bombed Pearl Harbor? No!
Otter: Germans?
Boon: Forget it, he's rolling.
--------------------------------------------------

 

[gmmastros]

You might want to try this...

UPDATE Tmpwpmas
SET    Tmpwpmas.wpmas_dt_billed = NullIf(i.wpmas_dt_billed,'1/1/1900'),
       Tmpwpmas.wpmas_dt_complet = NullIf(i.wpmas_dt_complet, '1/1/1900'),
       Tmpwpmas.wpmas_dt_pickup = NullIf(i.wpmas_dt_pickup, '1/1/1900'),
       Tmpwpmas.wpmas_dt_schrtn = NullIf(i.wpmas_dt_schrtn, '1/1/1900'),
       Tmpwpmas.wpmas_dt_shipped = NullIf(i.wpmas_dt_shipped, '1/1/1900'),
       Tmpwpmas.wpmas_dt_started = NullIf(i.wpmas_dt_started, '1/1/1900')
FROM   Tmpwpmas d
       INNER JOIN Inserted i on d.id_num = i.id_num
WHERE  i.wpmas_dt_billed = '1/1/1900'
       or i.wpmas_dt_complet = '1/1/1900'
       or i.wpmas_dt_pickup  = '1/1/1900'
       or i.wpmas_dt_schrtn  = '1/1/1900'
       or i.wpmas_dt_shipped = '1/1/1900'
       or i.wpmas_dt_started = '1/1/1900'

Make sure you have a good backup and run this on a DEV database first.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ousoonerjoe]

Thank you, George.

I knew there had to be a better way of handling the NULLS in a single statement. The Modify fields were a simple drop in.

Thanks.

--------------------------------------------------
Bluto: What? Over? Did you say "over"? Nothing is over until we decide it is! Was it over when the Germans bombed Pearl Harbor? No!
Otter: Germans?
Boon: Forget it, he's rolling.
--------------------------------------------------

 

[gmmastros]

You added the modify fields to the query I show above? You may not want to do that because you could be missing some updates. Specifically, if all of the date columns have good values, the where clause will filter out all the rows and the modify fields will not get updated.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ousoonerjoe]

Ah... good point. I completely over looked that. 2 calls is still much better than 7. I'll make the adjustment.

Thanks again.

--------------------------------------------------
Bluto: What? Over? Did you say "over"? Nothing is over until we decide it is! Was it over when the Germans bombed Pearl Harbor? No!
Otter: Germans?
Boon: Forget it, he's rolling.
--------------------------------------------------