So I've created a trigger that compares update before and after and determines if specific fields, specified in the where clause, have changed. If so, I insert a snapshot of the prior information into a history table.
The problem is when the field is created with null values (given the business rules, it is legitimately null) and when an update is made, it is unable to evaluate that the string is not equal to the null value. I want to capture that it was sent to us empty and later filled in.
What approach should I use to compare to null fields without impacting performance?
This is for SQL Server 2005
CREATE TRIGGER [prj].[TRG_Master_Projection_Upd_History] ON [prj].[Master_Projections]
AFTER UPDATE
AS
SET NOCOUNT ON -- Prevents the error that gets thrown after inserting multiple records
-- if multiple records are being updated
BEGIN
INSERT INTO prj.History_Projections (ProjectionID, Cancelled, Appeal, Description, Response_PatternID,
Proj_Mail_date, [3602_Mail_Date], Proj_Qty, [3602_Qty], Proj_Resp_Rate,
Bounce_Back, Nickels, Kits, Oversized_RE, ChangeComments,
Modification_Process, Modification_Date, Modification_User)
SELECT D.ProjectionID, D.Cancelled, D.Appeal, D.Description, D.Response_PatternID, D.Proj_Mail_Date,
D.[3602_Mail_Date], D.Proj_Qty, D.[3602_Qty], D.Proj_Resp_Rate, D.Bounce_Back, D.Nickels, D.Kits,
D.Oversized_RE, D.ChangeComments, D.Modification_Process, D.Modification_Date, D.Modification_User
FROM deleted as D
JOIN inserted as I
ON D.ProjectionID = I.ProjectionID
WHERE (I.Cancelled <> D.Cancelled
OR I.Appeal <> D.Appeal
OR I.Description <> D.Description
OR I.Response_PatternID <> D.Response_PatternID
OR I.Proj_Mail_Date <> D.Proj_Mail_Date
OR I.[3602_Mail_Date] <> D.[3602_Mail_Date]
OR I.Proj_Qty <> D.Proj_Qty
OR I.[3602_Qty] <> D.[3602_Qty]
OR I.Proj_Resp_Rate <> D.Proj_Resp_Rate
OR I.Bounce_Back <> D.Bounce_Back
OR I.Nickels <> D.Nickels
OR I.Kits <> D.Kits
OR I.Oversized_RE <> D.Oversized_RE )
END;
SET NOCOUNT OFF;