NetTalk Central

Author Topic: Audit Tables and Nettalk Server  (Read 3986 times)

MikeR

  • Sr. Member
  • ****
  • Posts: 260
    • View Profile
    • Email
Audit Tables and Nettalk Server
« on: May 31, 2017, 10:45:19 PM »
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

  • Sr. Member
  • ****
  • Posts: 430
    • View Profile
    • Email
Re: Audit Tables and Nettalk Server
« Reply #1 on: May 31, 2017, 11:30:00 PM »
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

  • Sr. Member
  • ****
  • Posts: 260
    • View Profile
    • Email
Re: Audit Tables and Nettalk Server
« Reply #2 on: June 01, 2017, 08:42:32 PM »
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

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 11244
    • View Profile
Re: Audit Tables and Nettalk Server
« Reply #3 on: June 02, 2017, 02:48:36 AM »
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