Trigger - Need an easier way to write

[baldwingrand]

I have a very simple trigger that is supposed to update a table for any change in value in a table. The problem is, I have about 20 columns that are capable of being updated. The only way I know how to write this is using IF statements. Is there a better/easier/shorter way to write this? I don't really want to write 20 IF statements. The code below uses two columns as an example. Thanks!


CREATE Trigger [trgIPIssueInput_upd] On [dbo].[IPIssueInput]

After Update

As

Begin
If Update (IPIssueType)
Begin
Insert Into IPIssueInputHistory (IPIssueRecID, UpdateDate, ChangeType, NewValue, OldValue)

Select
Inserted.IPIssueRecID, getDate()
, Case When
Deleted.IPIssueType Is Null
Then
'Issue Type Defined'
Else
'Issue Type Redefined'
End
, Inserted.IPIssueType
, Deleted.IPIssueType

From Deleted
Join Inserted On Deleted.IPIssueRecID = Inserted.IPIssueRecID
Where (Deleted.IPIssueType <> Inserted.IPIssueType)
Or Deleted.IPIssueType Is Null
Or Inserted.IPIssueType Is Null
End


If Update (IPIssueTitle)
Begin
Insert Into IPIssueInputHistory (IPIssueRecID, UpdateDate, ChangeType, NewValue, OldValue)

Select
Inserted.IPIssueRecID, getDate()
, Case When
Deleted.IPIssueTitle Is Null
Then
'Issue Title Defined'
Else
'Issue Title Redefined'
End
, Inserted.IPIssueTitle
, Deleted.IPIssueTitle

From Deleted
Join Inserted On Deleted.IPIssueRecID = Inserted.IPIssueRecID
Where (Deleted.IPIssueTitle <> Inserted.IPIssueTitle)
Or Deleted.IPIssueTitle Is Null
Or Inserted.IPIssueTitle Is Null
End

End

[tattooedscorpdc]

You could always create a matrix table in the database that the triggerreferences before it goes to the 20 conditional statements you would have to write other wise. Similiar methodology but less conditionals would need to be written as the conditions would reside in the table that is referenced as opposed to the code... that said though I would suggest writing one conditional that will apear large that uses the else condition... it would be one statement as opposed to 20.

Hope this helps,

Larry D
MS Architect Evangelist