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
Step 1 - Create the ErrorTable to Store errors
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]
Step 2 - Create the Triggers on the appropriate tables
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
- - SET NOCOUNT ON added to prevent extra result sets from
- - interfering with SELECT statements.
SET NOCOUNT ON;
- - 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