Trigger after update previous value.

I do this with SQL stored procedures all the time.   Basically .. create your audit table as a mirror of your main table (all columns)... plus additional timestamp.  Then.. as Ninox only has a table after update trigger... In that trigger copy the row out to the audit table.. setting each column .. and setting the audit timestamp to now() where the audit Id = this.Id and audit audit timestamp is NULL .. (Disclaimer.. I have not tried NULLs in Ninox yet)...      

In essence.. the top row of the logically keyed record will be the same as the Id record on the main table.  it's audit timestamp will be NULL.. and you will set the previously null row to current time.. Hence.. you know when that audit record "closed"...... You will have one row for every row "update".. 

In other applications.. I have also seen this to be a more complex concatenate comparing each column from the old to the new... and setting which fields have changed. 

Hope this helps. 

OK.. So I am still "learning" how the triggers work.  Playing around.. from a button that updates two fields.. TWO table after update triggers fire.  I would have expected ONE.. but I guess Ninox updates after each field update.   Not bad. but just know if you update two fields on the same row.. You will hae two rows in the audit table... at least by my current strategy.. :( 

I will keep playing.. 

When creating your audit table. Once you have everything you want.. You may want to "hide" it..  Once you do that.... NO ONE can see the table.. As and Administrator.. you can go to the Data Model.. find your table.. click on it.. and then unhide it.  :)

Hi there, 

We have a little demo database for that theme in our Webinar team EN 2018. I have already invited you both to the team. Please accept the invitation in order to get access to the team. The name of the database is 28_Change_history. Feel free to have a look on it (as well as in the other databases that might be of interest for you as well). 

Best, Jörg

Hi Jörg,

can you invite me for demo databases?

Nick

Jörg,

Thanks.. I downloaded a back up of it.. Interesting that you put the code at the FIELD level.. I would have expected it at the table level.. this way.. if I have N attributes (aka fields) .. I only put the code in one spot..  I will play around with it. 

I know this is an old post but I am trying to see previous value of the field in a column level after update trigger...  how do I do this?  Thank you.

I know this is an old post but I am trying to see previous value of the field in a column level after update trigger...  how do I do this?  Thank you.