1

I am able to find out the columns updated within the trigger of the table. However the trigger is kind of big, I want to reduce its size as much as possible. So now, I want to create a generic stored procedure and find out the updated columns from within the stored procedure.

Here is the SQL query that finds out the updated columns

SELECT  @idTable = T.id 
FROM    sysobjects P JOIN sysobjects T ON P.parent_obj = T.id 
WHERE   P.id = @@PROCID

---- Get COLUMNS_UPDATED if update
DECLARE @Columns_Updated VARCHAR(50)

SELECT @Columns_Updated = ISNULL(@Columns_Updated + ', ', '') + name 
FROM syscolumns 
WHERE id = @idTable
  AND CONVERT(VARBINARY,REVERSE(COLUMNS_UPDATED())) & POWER(CONVERT(BIGINT, 2), colorder - 1)  > 0

Could some one help me out as to what am I suppose to do to achieve my goal

ik024
  • 156
  • 3
  • 14
  • @marc_s i dont know if its the right ans to your question. But i am using sql server. Please bear with me if i am wrong – ik024 Sep 17 '13 at 12:54
  • Check http://stackoverflow.com/questions/2136861/t-sql-what-columns-have-changed-after-an-update and http://stackoverflow.com/questions/8049810/how-to-get-updated-column-names-in-sql-server – Yuriy Galanter Sep 17 '13 at 15:19
  • I HAD ALREADY LOOKED THRU THOSE BUT DINT HELP ME OUT – ik024 Sep 18 '13 at 05:06
  • I AM ACTUALLY LOOKING FOR A GENERIC STORE PROCEDURE TO AUDIT THE TABLE – ik024 Sep 18 '13 at 05:22
  • Writing in all caps is considere SHOUTING. –  Sep 24 '13 at 20:31

1 Answers1

0

If you want to create an sp that will execute whenever you want and see what was updated database-wide since the last run of this sp, then I don't think it can be done. I would advise to either use the built-in sql server 2008 audit functionality or use triggers as Yuriy Galanter already pointed out.

alas
  • 148
  • 8