DBA_Learner (9/12/2012)

---

Hello All,

We all know that in SQL Server standard edition we cannot do any auditing on Server and DB Level. So, alternative for this is to create triggers. Now my questions is, I would like to audit all tables(300) on my database. I am using is standard edition. The alternative solution I thought is of having DML and DDL triggers on whole database. Now for DML triggers I need to create 300 triggers to audit activity on each table. So, by creating these many triggers will there be any performance impact or will be there be any issues. Any suggestions??

DBA_Learner (9/12/2012)

---

Ya, I am not implementing any spcific logic..here is the way i am doing..

CREATE TABLE LOG_TABLE (Add_dttm datetime DEFAULT (GetDate()), TABLE_NAME sysname, Activity char(6),login_sname sysname DEFAULT SUSER_SNAME())
GO

DECLARE @sql varchar(8000), @TABLE_NAME sysname
SET NOCOUNT ON

SELECT @TABLE_NAME = MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables

WHILE @TABLE_NAME IS NOT NULL
BEGIN
SELECT @sql = 'CREATE TRIGGER [' + @TABLE_NAME + '_Usage_TR] ON [' + @TABLE_NAME +'] '
+ 'FOR INSERT, UPDATE, DELETE AS '
+ 'IF EXISTS (SELECT * FROM inserted) AND NOT EXISTS (SELECT * FROM deleted) '
+ 'INSERT INTO LOG_TABLE (TABLE_NAME,Activity) SELECT ''' + @TABLE_NAME + ''', ''INSERT''' + ' '
+ 'IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted) '
+ 'INSERT INTO LOG_TABLE (TABLE_NAME,Activity) SELECT ''' + @TABLE_NAME + ''', ''UPDATE''' + ' '
+ 'IF NOT EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted) '
+ 'INSERT INTO LOG_TABLE (TABLE_NAME,Activity) SELECT ''' + @TABLE_NAME + ''', ''DELETE''' + ' GO'
SELECT @sql
EXEC(@sql)
SELECT @TABLE_NAME = MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables WHERE TABLE_NAME > @TABLE_NAME
END
SET NOCOUNT OFF



Also, just want to know how about doing server side tracing using tsql code..will that work?

DBA_Learner (9/13/2012)

---

CDC captures only insert,update,delete on tables..but I don't think it captures drop,truncate,select ...Also in order to create that we need sql agent which on back ground runs the jobs..It requires separate set of system tables and need to enable cdc on each table. I think server side scripting will be more better interms of creation and performance..