Create Multi Trigger for Audit Table Automatic

Create Proc sp_CreateAuditTable
@table_name SYSNAME
As
--Declare @table_name SYSNAME
--SELECT @table_name = 'dbo.Dtl_Recipe_Items'

DECLARE
      @object_name SYSNAME
    , @object_id INT

SELECT
      @object_name = '[' + s.name + '].[' + o.name + '_A]'
    , @object_id = o.[object_id]
FROM sys.objects o WITH (NOWAIT)
JOIN sys.schemas s WITH (NOWAIT) ON o.[schema_id] = s.[schema_id]
WHERE s.name + '.' + o.name = @table_name
    AND o.[type] = 'U'
    AND o.is_ms_shipped = 0

Print @object_id

DECLARE @SQL NVARCHAR(MAX) = ''

;WITH index_column AS
(
    SELECT
          ic.[object_id]
        , ic.index_id
        , ic.is_descending_key
        , ic.is_included_column
        , c.name
    FROM sys.index_columns ic WITH (NOWAIT)
    JOIN sys.columns c WITH (NOWAIT) ON ic.[object_id] = c.[object_id] AND ic.column_id = c.column_id
    WHERE ic.[object_id] = @object_id
)
SELECT @SQL = 'CREATE TABLE ' + @object_name + CHAR(13) + '(' + CHAR(13) + STUFF((
    Select Columns from
(
Select CHAR(9) + ', [Id] INT NOT NULL IDENTITY(1,1)' + CHAR(13) as Columns, 0 as column_id
UNION
SELECT CHAR(9) + ', [' + c.name + '] ' +
CASE WHEN c.is_computed = 1
THEN 'AS ' + cc.[definition]
ELSE UPPER(tp.name) +
CASE WHEN tp.name IN ('varchar', 'char', 'varbinary', 'binary', 'text')
  THEN '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length AS VARCHAR(5)) END + ')'
WHEN tp.name IN ('nvarchar', 'nchar', 'ntext')
  THEN '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length / 2 AS VARCHAR(5)) END + ')'
WHEN tp.name IN ('datetime2', 'time2', 'datetimeoffset')
  THEN '(' + CAST(c.scale AS VARCHAR(5)) + ')'
WHEN tp.name = 'decimal'
  THEN '(' + CAST(c.[precision] AS VARCHAR(5)) + ',' + CAST(c.scale AS VARCHAR(5)) + ')'
ELSE ''
END +
CASE WHEN c.collation_name IS NOT NULL THEN ' COLLATE ' + c.collation_name ELSE '' END +
CASE WHEN c.is_nullable = 1 THEN ' NULL' ELSE ' NOT NULL' END +
CASE WHEN dc.[definition] IS NOT NULL THEN ' DEFAULT' + dc.[definition] ELSE '' END --+
--CASE WHEN ic.is_identity = 1 THEN ' IDENTITY(' + CAST(ISNULL(ic.seed_value, '0') AS CHAR(1)) + ',' + CAST(ISNULL(ic.increment_value, '1') AS CHAR(1)) + ')' ELSE '' END
END + CHAR(13),c.column_id
FROM sys.columns c WITH (NOWAIT)
JOIN sys.types tp WITH (NOWAIT) ON c.user_type_id = tp.user_type_id
LEFT JOIN sys.computed_columns cc WITH (NOWAIT) ON c.[object_id] = cc.[object_id] AND c.column_id = cc.column_id
LEFT JOIN sys.default_constraints dc WITH (NOWAIT) ON c.default_object_id != 0 AND c.[object_id] = dc.parent_object_id AND c.column_id = dc.parent_column_id
LEFT JOIN sys.identity_columns ic WITH (NOWAIT) ON c.is_identity = 1 AND c.[object_id] = ic.[object_id] AND c.column_id = ic.column_id
WHERE c.[object_id] = @object_id
)a
ORDER BY a.column_id
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, CHAR(9) + ' ')
    --+ ISNULL((SELECT CHAR(9) + ', CONSTRAINT [' + k.name + '] PRIMARY KEY (' +
    --                (SELECT STUFF((
    --                     SELECT ', [' + c.name + '] ' + CASE WHEN ic.is_descending_key = 1 THEN 'DESC' ELSE 'ASC' END
    --                     FROM sys.index_columns ic WITH (NOWAIT)
    --                     JOIN sys.columns c WITH (NOWAIT) ON c.[object_id] = ic.[object_id] AND c.column_id = ic.column_id
    --                     WHERE ic.is_included_column = 0
    --                         AND ic.[object_id] = k.parent_object_id
    --                         AND ic.index_id = k.unique_index_id    
    --                     FOR XML PATH(N''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, ''))
    --        + ')' + CHAR(13)
    --        FROM sys.key_constraints k WITH (NOWAIT)
    --        WHERE k.parent_object_id = @object_id
    --            AND k.[type] = 'PK'), '')
+ ')'  + CHAR(13)

PRINT @SQL
EXEC (@SQL)


-------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------

GO


Create Proc sp_CreateTrigger
@tables nvarchar(max)
AS
BEGIN
Declare @tbl nvarchar(200)
Declare @cur Cursor

Set @cur = Cursor for
Select name from sys.tables Where name in (Select splitdata from dbo.fnSplitString(@tables,','))
OPEN @cur
Fetch Next from @cur into @tbl
While @@fetch_status=0    
BEGIN
Declare @sql nvarchar(max)
Set @sql = 'EXEC sp_CreateAuditTable ''dbo.' + @tbl + ''''
Print @sql
EXEC(@sql)

Set @sql = 'ALter Table ' + @tbl + '_A Add InsertedOn Datetime default Getdate()'
Print @sql
EXEC(@sql)

Set @sql = 'Create trigger dbo.' + @tbl + '_Add on dbo.' + @tbl + '
after INSERT AS
BEGIN
INSERT INTO ' + @tbl + '_A
SELECT *,Getdate() FROM INSERTED
END'
Print @sql
EXEC(@sql)

Set @sql = 'Create trigger  dbo.' + @tbl + '_Delete on dbo.' + @tbl + '
after Delete AS
BEGIN
INSERT INTO ' + @tbl + '_A
SELECT *,Getdate() FROM DELETED
END'
Print @sql
EXEC(@sql)

Set @sql = 'Create trigger dbo.' + @tbl + '_Update on dbo.' + @tbl + '
after Update AS
BEGIN
INSERT INTO ' + @tbl + '_A
SELECT *,Getdate() FROM DELETED
END'
Print @sql
EXEC(@sql)

Fetch Next from @cur into @tbl
END

Close @cur    
Deallocate @cur
END