EverSQL Database Performance Knowledge Base
Create trigger to Audit data changes SQL Server
Database type:
Good night folks,
I need to create a stored Procedure that giving the name of a table by parameter create a trigger to Audit data changes in the table.
I need to do something very similar to the code of this article, but for performance, reasons I need to have a Form in my program that allows me to choose the tables and the main fields to audit.
http://weblogs.asp.net/jongalloway/adding-simple-trigger-based-auditing-to-your-sql-server-database
I have tried changing the code in various ways with cursors etc unsuccessful because I have not much experience with T-SQL.
My main doubt is how to give the table name and the fields that I want (which can be 2 or 3 or more) by parameter in my stored procedure.
Select field = 0,maxfield = max (ORDINAL_POSITION)
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME =tablename
while field <maxfield
begin
select field = min (ORDINAL_POSITION)
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME =tablename
and ORDINAL_POSITION> @ field
select bit = (field - 1)% 8 + 1 =bit select power (2, @ bit - 1)
select char = ((field - 1) / 8) + 1 if substring (COLUMNS_UPDATED () , @ char, 1) &bit> 0 or type in ('I', 'D')
I need something like:
select *
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = tablename
and COLUMN_NAME = 'My FIELD'
I will be very grateful if you can help me, because I am trying to change the code almost for two days without much success.