Góc thư viện kiến thức sưu tầm

 IF OBJECT_ID('GenerateTriggers','P') IS NOT NULL        DROP PROC GenerateTriggers    GO        CREATE PROC GenerateTriggers     @Schemaname Sysname = 'dbo'    ,@Tablename  Sysname    ,@GenerateScriptOnly    bit = 1   ,@ForceDropAuditTable   bit = 0   ,@IgnoreExistingColumnMismatch   bit = 0   ,@DontAuditforUsers NVARCHAR(4000) =  '' ,@DontAuditforColumns NVARCHAR(4000) =  '' AS        SET NOCOUNT ON        /*    Parameters    @Schemaname            - SchemaName to which the table belongs to. Default value 'dbo'.    @Tablename            - TableName for which the procs needs to be generated.    @GenerateScriptOnly - When passed 1 , this will generate the scripts alone..    ...

  Audit is always a kind of standard requirement in all the trasactional system. For auditing a particular table ,we need to create a similar structure audit table and create Insert , Update and Delete triggers to keep track of the changes in the table. This is utility procedure will create below objects in the database of the passed table. 1. Creates an audit table with name <Tablename>_audit. It has some additional fields to capture the changes on the table                   AuditDataState   -- Stores the data state whether "New" or "Old"                   AuditDMLAction   -- Stores the DML Action like "Insert","Update","Delete"                   AuditUser        ...