Need Update Trigger with Version number in the History Table

Transact-SQL
1

Hello All,

I need a version(sequence) number in the history table, how can I do that ?

MyTable
ID
Value

MyTable_History
ID
MyTableID
Version
Value

how can I update the version number.

Its an update trigger only

please do the needful.

Many Thanks
Amjath

2

The update trigger is on value in MyTable?

Use the special rows INSERTED and DELETED within the update trigger
SELECT ID AS MyTableID, GETDATE() AS Version, Value INTO MyTable_History FROM DELETED

I used getdate as the version number.

3

Might not be unique? (if inserts are very frequent)

4

Just use an IDENTITY column. The versions won't be sequential but, for storage, it's not going to matter.

5

CREATE TRIGGER [dbo].[DI_M_AttestationSnapshotAction_Trigger] ON [dbo].[DI_M_AttestationSnapshotAction]
AFTER UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

INSERT INTO [dbo].[DI_M_AttestationSnapshotAction_History]
       (  [Version]
		  ,[AttestationSnapshotActionId]
		  ,[TaskId]
		  ,[Approve]
		  ,[Revoke]
		  ,[Comment]
		  ,[EffectiveDate]
		  ,[ReviewerId]
		  ,[LastModifiedBy]
		  ,[LastModifiedDate])

SELECT (select (ISNULL(Max([version]), 0) + 1) from DI_M_AttestationSnapshotAction_History where AttestationSnapshotActionid = d.[Id])
	  ,d.[Id]
	  ,d.[TaskId]
	  ,d.[Approve]
	  ,d.[Revoke]
	  ,d.[Comment]
	  ,d.[EffectiveDate]
	  ,d.[ReviewerId]
	  ,d.[LastModifiedBy]
	  ,d.[LastModifiedDate]
	FROM INSERTED i
	JOIN DELETED d ON i.[Id] = d.[Id]
	WHERE 
	i.[Approve] <> d.[Approve] OR
	i.[Revoke] <> d.[Revoke] OR
	i.[Comment] <> d.[Comment] OR
	i.[TaskId] <> d.[TaskId] OR
	i.[EffectiveDate] <> d.[EffectiveDate] OR
	i.[ReviewerId] <> d.[ReviewerId]

END

is this fine ? any performance issue ?

6

Looks OK to me. Note that

i.[Approve] <> d.[Approve] OR
...

will NOT detect if one/both of the columns are NULL, so this is only safe if those columns can never contain NULLs

I would add a [ChangedDate] DATETIME column to [DI_M_AttestationSnapshotAction_History] and just store GetDate() in it, which would tell you when the change had occurred. Also the UserID if you are able to store that too (we have an [UpdatedBy] column in all our tables, so that gets included in the list of columns stored in our History Tables.

(select (ISNULL(Max([version]), 0) + 1) from DI_M_AttestationSnapshotAction_History where AttestationSnapshotActionid = d.[Id])

That needs an index on [Id], [version], but would then be efficient. Do you absolutely need a version number, or just "chronological order" of changes?

We only use ChronologicalOrder, based on the [ChangedDate] column in our History Tables. We don't have a 1,2,3 ... version number, but it hasn't seemed to matter in our case, folk are just interested in WHAT changed and WHEN and sometimes also: WHO made the change? :slight_smile:

Much cheaper to just store GetDate() in a column than search for Max([version]

One thought, but I haven't tested it, but I think that

(select (ISNULL(Max([version]), 0) + 1) from DI_M_AttestationSnapshotAction_History where AttestationSnapshotActionid = d.[Id])

will give you NULL for the first record (i.e. when there are not rows in HISTORY for that ID), you might have to use:

ISNULL( (select Max([version]) from DI_M_AttestationSnapshotAction_History where AttestationSnapshotActionid = d.[Id]) , 0) + 1)
7

how to solve this any idea.... ??
i have a comments field, which might come as NULL...

please suggest

8

something like this will solve this issue
FROM INSERTED i
JOIN DELETED d ON i.[Id] = d.[Id]
WHERE
i.[Approve] <> d.[Approve] OR
i.[Revoke] <> d.[Revoke] OR
ISNULL(i.[Comment], '') <> ISNULL(d.[Comment], '') OR
--i.[TaskId] <> d.[TaskId] OR
ISNULL(i.[EffectiveDate], CONVERT(date, getdate())) <> ISNULL(d.[EffectiveDate], CONVERT(date, getdate()))

9 
...
(
       i.[EffectiveDate] <> d.[EffectiveDate] 
    OR (i.[EffectiveDate] IS NULL AND d.[EffectiveDate] IS NULL) 
) OR
...

I suspect using IsNull() with a bogus value (GetDate() in your example) will be slower