Hiya guys, PLEASE help me with this problem. I need keep track of every single field change of all tables within the database and store the new/old/time values into a log table. One of the tables that I need to keep track of has about 120 columns. So when a new row is inserted into this table, i need to create 120 rows in the log table (one for each field change, in this case insertion). Forget about the bad design and performance issues and focus on the technical problem please. So my questions are:
Note: everything is done within the insert/update/delete trigger
1) Is there some elegant built-in sybase functions that let me do this within the insert/update/delete trigger?
2) How do I do a "select column from table" but NOT hardcoding in the column name. Something like:
select column(k) from table
bla bla bla
can someone convert this java psuedo code into transact-sql?
3) declare @colname varchar(200), @value varchar(255)
select @colname = "name_of_a_column"
select @value = (select @colname from table) OR
select @value = @colname from table
Why does this not work? it keep giving me the NAME of the column instead of the actual value within the column.
4) execute ( "select "+@nameofcolumn+" from table")
execute ("select "+@nameofcolumn+" from inserted")
this does NOT work. both are done within the trigger, why?
As you can see, i am trying to do a while loop and separate the column fields in the inserted/deleted temp tables and then insert each column field into the log table. I am using the latest version of sybase ase. Any help or suggestions will be greatly appreciated, thank you!