Audit Tables and Nettalk Server

[MikeR]

My client wants to store history of all changes to there data.
I am running on sql server
I have set up a shadow table in sql for each table that requires an audit.
I have extra fields like date changed, Insert,change,orDelete, Guid, and UserID
I have set a trigger on the original table to set the extra fields, except UserId
The userid I can access from the trigger is the userid logged onto sql server in the connection string and this is not correct.
I need to set the field to a nettalk sessionvalue I have stored when the user signs on.

I have got around this problem by adding a userid on the original table.
This ensures the correct info is inserted into the shadow table.

My question to the user group is there a single place I can set the userid on the table before inserts ,changes. , deletes happen

I know I can use embed points in each form and browse and in all hand code that modifies these tables.
I am looking for a smarter way.
There are triggers in the clarion dictionary, but how do I access the p_web object ?

[Niels Larsen]

Hi

MSSQL 2016 can handle temporal tables.
Take a look at: https://www.mssqltips.com/sqlservertip/3680/introduction-to-sql-server-2016-temporal-tables/

Maybe this is a solution....

/Niels

[MikeR]

Thanks Niels, this feature looks great I will look into it.

Do you know if there is a single embed point where I can set a userid filed in a table on insert,change,delete to a session value..
Instead of doing it on each form, browse and source code that updates the table ?

[Bruce]

a) create a global, THREADED variable (say: glo:userId)

b) In WebHandler, processLink method, before parent call, set
glo:UserId = p_web.GSV('userid')

c) In global FileManager embeds, or Dict Client-side triggers, you can then use glo:UserId.

cheers
Bruce