/*
Created 29th Jan 2007: PI
Script to add audit triggers to all user tables. Iterates through the table list and calls "AllTriggers.sql" each time using SQLCMD.
You'll need to change the Environment variables (:setvar) and the path to "AllTriggers.sql" then just run it.
*/


-- Uses SQLCMD to execute a trigger script. The script is currenlty looked for at "d:\paul\AllTriggers.sql" so just change this path appropriately.
:setvar triggerusername yourusername
:setvar triggerpassword yourpassword
:setvar databasename yourdatabasename

-- Firstly remove the existing audit triggers
declare @trigname varchar(1000)
declare trigger_cursor CURSOR FOR
select name from sys.triggers
where name like 'tr_Audit%'

OPEN trigger_cursor
FETCH NEXT FROM trigger_cursor
INTO @trigname
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @trigname
exec('drop trigger ' + @trigname)
FETCH NEXT FROM trigger_cursor
INTO @trigname
END
CLOSE trigger_cursor
DEALLOCATE trigger_cursor

declare @tablename varchar(1000)
declare @sql varchar(1000)

-- Loop through the tables but avoid adding triggers on certain tables - add extra clauses here if necessary.
declare tables_cursor CURSOR FOR
select table_name from information_schema.tables
where table_name not like 'tmp%'
and table_name not like 'dt%'
and table_type <> 'VIEW'
order by table_name

OPEN tables_cursor
FETCH NEXT FROM tables_cursor
INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @tablename
set @sql = 'sqlcmd -U $(triggerusername) -P $(triggerpassword) -S localhost -d $(databasename) -i d:\paul\AllTriggers.sql -v tablename="' + @tablename + '"'
exec xp_cmdshell @sql
FETCH NEXT FROM tables_cursor INTO @tablename
END
CLOSE tables_cursor
DEALLOCATE tables_cursor
GO

-- Add the audit table if it doesn't exist.
use $(databasename)
go
if not exists (select * from sysobjects where id = object_id(N'[dbo].[Audit]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
create table Audit
(
Audit_Trail_ID int identity(1,1),
Audit_TableName varchar(128) null,
Audit_Operation varchar(50) null,
Audit_Details varchar(max) null,
UpdateDate datetime null,
UserName varchar(128) null
)
go