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.
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.
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = INFORMATION_SCHEMA.COLUMNS.tablename AND INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME = 'My FIELD'