USE [GeneraliDW]
GO

/****** Object:  StoredProcedure [dbo].[spCreateAuditTable]    Script Date: 28/11/2012 15:38:32 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================

-- Author:	 <Author,,Name>

-- Create date: <Create Date,,>

-- Description:	<Description,,>

-- =============================================

CREATE PROCEDURE [dbo].[spCreateAuditTable]

        -- Add the parameters for the stored procedure here
        @TableName nvarchar(100)
AS

BEGIN

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


    --DECLARE @TABLENAME varchar(100)
        DECLARE @SCHEMA varchar(100)
        --SET @TABLENAME = N'Company'
        SET @SCHEMA = N'dbo'


        DECLARE @Done bit 
        SET @Done=0 


        DECLARE @CRLF char(2)
        SET @CRLF = char(10)
        DECLARE @SQL varchar(2000)
        SET @SQL = 'IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''['+@SCHEMA+'].['+@TABLENAME+'_Audit]'') AND type in (N''U''))
        DROP TABLE ['+@SCHEMA+'].['+@TABLENAME+'_Audit] CREATE TABLE ['+@SCHEMA+'].['+@TABLENAME+'_Audit] ('+@CRLF

        DECLARE @COLUMNID int
        SET @COLUMNID = 0
        DECLARE @COLUMNNAME varchar(1000)
        DECLARE @COLUMNTYPE varchar(100)
        DECLARE @COLUMNSIZE int


        WHILE @Done=0   
        BEGIN            
                SELECT top 1
                @COLUMNID=clmns.column_id,
                @COLUMNNAME=clmns.name ,
                @COLUMNTYPE=usrt.name ,
                @COLUMNSIZE=CAST(CASE WHEN baset.name IN (N'nchar', N'nvarchar') AND 
        clmns.max_length <> -1 THEN clmns.max_length/2 ELSE clmns.max_length END AS int)
                FROM
                sys.tables AS tbl
                INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tbl.object_id
                LEFT OUTER JOIN sys.types AS usrt ON usrt.user_type_id = clmns.user_type_id
                LEFT OUTER JOIN sys.types AS baset ON baset.user_type_id = clmns.system_type_id and 
        baset.user_type_id = baset.system_type_id
                WHERE
                (tbl.name=@TABLENAME and SCHEMA_NAME(tbl.schema_id)=@SCHEMA)
                and clmns.column_id > @COLUMNID
                ORDER BY
                clmns.column_id asc


                IF @@rowcount=0  
                        begin   
                                SET @Done=1  
                        end
                else
                        begin
                                SET @SQL=@SQL+'        ['+@COLUMNNAME+'] ['+@COLUMNTYPE+'] '
                                IF (@COLUMNTYPE='nchar' OR @COLUMNTYPE='nvarchar' OR @COLUMNTYPE='varchar') SET @SQL=@SQL+'('+ltrim(str(@COLUMNSIZE))
        +') '
                                SET @SQL=@SQL+'NULL, '+@CRLF
                        end
        END


        SET @SQL=@SQL+'        ['+@TABLENAME+'UpdateDate] datetime,
        ['+@TABLENAME+'UpdateAction] nvarchar(10) ) '

        --print @SQL
        exec (@SQL)


        SET @SQL='
		
		IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N''['+@SCHEMA+'].['+@TABLENAME+'_Trigger_Update]''))
        DROP TRIGGER ['+@SCHEMA+'].['+@TABLENAME+'_Trigger_Update]
		
		IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N''['+@SCHEMA+'].['+@TABLENAME+'_Trigger_Delete]''))
        DROP TRIGGER ['+@SCHEMA+'].['+@TABLENAME+'_Trigger_Delete]

		IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N''['+@SCHEMA+'].['+@TABLENAME+'_Trigger_Insert]''))
        DROP TRIGGER ['+@SCHEMA+'].['+@TABLENAME+'_Trigger_Insert]
'

        --print @SQL
        exec(@SQL)


        SET @SQL='CREATE TRIGGER ['+@SCHEMA+'].['+@TABLENAME+'_Trigger_Update] 
   ON  ['+@SCHEMA+'].['+@TABLENAME+'] 
   AFTER UPDATE
AS

BEGIN

        SET NOCOUNT ON;
        INSERT INTO '+@TABLENAME+'_Audit 
        SELECT *,getdate(),''Update'' FROM inserted END

'

        exec (@SQL)
        --print @SQL


        SET @SQL='CREATE TRIGGER ['+@SCHEMA+'].['+@TABLENAME+'_Trigger_Delete] 
   ON  ['+@SCHEMA+'].['+@TABLENAME+'] 
   AFTER DELETE
AS

BEGIN

        SET NOCOUNT ON;
        INSERT INTO '+@TABLENAME+'_Audit 
        SELECT *,getdate(),''Delete'' FROM deleted END

'

        exec (@SQL)
        --print @SQL


        SET @SQL='CREATE TRIGGER ['+@SCHEMA+'].['+@TABLENAME+'_Trigger_Insert] 
   ON  ['+@SCHEMA+'].['+@TABLENAME+'] 
   AFTER INSERT
AS

BEGIN

        SET NOCOUNT ON;
        INSERT INTO '+@TABLENAME+'_Audit 
        SELECT *,getdate(),''Insert'' FROM inserted END

'

        exec (@SQL)
        --print @SQL
END

GO