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..                          When passed 0 , this will create the audit tables and triggers in the current database.                          Default value is 1    @ForceDropAuditTable - When passed 1 , will drop the audit table and recreate                         When passe

  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           -- Stores the User performed the action on the table                   AuditDateTime    -- Stores the date time on which the action happened. 2. Creates an insert trigger with the name <TableName>_Insert 3. Creates a delete trigger with the name <TableName&g