Explanation of SQL

This SQL creates triggers on the TransHeader, TransDetail, Account, and AccountContact tables that monitor the data for problems that may be caused by refresh problems. Specifically, this query compares the SeqID of the new record and makes sure it is larger than the SeqID of the current record. If it is not, an entry is created in the ErrorTable table (which must be created as part of this).

Risk of Data Corruption if Run Improperly

There is no risk of data corruption, but the ErrorTable could grow unnecessarily large if not periodically deleted.

SQL

CREATE TABLE [dbo].[ErrorTable](
    [TableName] [VARCHAR](25) NOT NULL,
    [ID] [INT] NOT NULL,
    [ClassTypeID] [INT] NOT NULL,
    [ModifiedByUser] [nvarchar](25) NULL,
    [ModifiedByComputer] [nvarchar](25) NULL,
    [ModifiedDate] [datetime] NOT NULL,
    [OldSeqID] [INT] NOT NULL,
    [NewSeqID] [INT] NOT NULL
 CONSTRAINT [ErrorTable_PK] PRIMARY KEY CLUSTERED
(
    [TableName], [ID], [ClassTypeID], [ModifiedDate]
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

In general, only the primary tables that are edited are monitored by this query. It could easily be extended, but the value of that would be limited except in special circumstances.

<code sql> –

– Author: Cyrious Software – Create date: 9/14/2012 – Description: SeqID Validator –

CREATE TRIGGER dbo.TransHeader_SeqIDErrors

 ON  dbo.TransHeader
 AFTER UPDATE

AS BEGIN

  1. - SET NOCOUNT ON added to prevent extra result sets from
  2. - interfering with SELECT statements.

SET NOCOUNT ON;

  1. - Insert statements for trigger here

insert into ErrorTable

     select 'TransHeader' as [TableName],
            Inserted.ID,
            Inserted.ClassTypeID,
            Inserted.ModifiedByUser,
            Inserted.ModifiedByComputer,
            GETDATE() as [ModifiedDate],
            Deleted.SeqID as OldSeqID,
            Inserted.SeqID as NewSeqID
     from Inserted join Deleted on Inserted.ID = Deleted.ID
     where Inserted.SeqID 
You could leave a comment if you were logged in.