Differences

This shows you the differences between two versions of the page.

Link to this comparison view

— control_sql_-_log_records_saved_without_changing_seqid [2019/01/27 11:29] (current)
Line 1: Line 1:
 +====== 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 =====
 +
 +
 +
 +<code 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]
 +</code>
 +
 +
 +
 +===== 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