Stack Overflow
Stack Overflow

Questions tagged [audit-tables]

73 questions
3
votes
2 answers

Audit Triggers: Use INSERTED or DELETED system tables

The topic of how to audit tables has recently sprung up in our discussions... so I like your opinion on whats the best way to approach this. We have a mix of both the approaches (which is not good) in our database, as each previous DBA did what…
UB.
  • 367
  • 5
  • 14
3
votes
3 answers

SQL Server Auditing Data in the Same Table

A project I'm working on requires that a record be digitally "signed" and after that any modifications would create a new "version" of the row. The "signed" record can't be modified for regulatory reasons and new versions shouldn't be modified very…
Jim Brown
  • 498
  • 1
  • 6
  • 20
2
votes
2 answers

Query help when using audit table

Assuming I have two tables, one with the following columns called lease_period: tenant_trading_name, suite_id, lease_id, building_id and another, called lease_period_audit with the following: audit_date, audit_type, tenant_trading_name, suite_id,…
Michael A
  • 9,480
  • 22
  • 70
  • 114
2
votes
1 answer

How to audit a @JoinTable with @ManyToMany

I'm working on a Spring-Boot project with a H2 database. I have two entities Portfolio and Report, and there is a many-to-many association between the two. I want those entities to be audited, so I followed this tutorial to audit through an…
neophat
  • 187
  • 1
  • 10
2
votes
0 answers

Audit trigger (Statement Level) storing identifier of inserted/updated/deleted rows

I have the following small MVWE for a basic micro-auditing system which works fine but lacks a functionality: DROP TABLE IF EXISTS audit CASCADE; CREATE TABLE audit( Id BIGSERIAL NOT NULL ,TimeValue TIMESTAMP NOT NULL …
jlandercy
  • 7,183
  • 1
  • 39
  • 57
2
votes
3 answers

Shadow table for rails model

I have Follow model in my rails app and when user follow some content - record created, unfollow - record deleted. I need track this changes in separate table (insert and delete only). It will be used to build some reports with plain SQL - thats why…
Volodymyr
  • 1,136
  • 3
  • 11
  • 28
2
votes
2 answers

Find out what application is writing to SQL table and when

Is there anyway to audit a SQL table where you can get information such as what application is writing to it and when it writes to it? There are a bunch of applications users are using here and I have combed through each on and cannot find where any…
Tom
  • 4,467
  • 17
  • 59
  • 91
1
vote
1 answer

select maximum value in inner query < another in an outer query

I have two auditing tables: Trip_aud and Event_aud. They were created in Envers, but I'm querying them with SQL. Their are basically the same as the domain tables, except for a revision value which is incremented every time there is a change and…
Bruno Kim
  • 2,300
  • 4
  • 17
  • 27
1
vote
1 answer

SQL Server 2005 Auditing

Background I have a production SQL Server 2005 server to which 4 different applications connect and make changes. There are no foreign keys and in some cases no primary keys. Unfortunately throwing the whole thing out and starting from scratch is…
Eva Lacy
  • 1,257
  • 10
  • 24
1
vote
4 answers

Is this a good design for an audit table with tons of records?

I have a table that tracks inventory data by each individual piece. This is a simplified version of the table (some non-key fields are excluded): UniqueID, ProductSKU, SerialNumber, OnHandStatus, Cost, DateTimeStamp Every time something happens…
Rob Sobers
  • 20,737
  • 24
  • 82
  • 111
1
vote
2 answers

How to join Audit table to Entity table using FetchXML in Dynamics 365 Customer Engagement

I would like to add a link to the Entity table in this query: I have tried to use the ObjectTypeCode attribute but link to the Product table, but there seems…
Maarten
  • 49
  • 8
1
vote
2 answers

Audit data changes with Debezium

I have a use case where I want to audit the DB table data changes into another table for compliance purposes. Primarily, any changes to the data like Inserts/Updates/Deletes should be audited. I found different options like JaVers, Hibernate Envers,…
SmartTechie
  • 135
  • 2
  • 10
1
vote
2 answers

Basic T-SQL trigger to populate an audit table

Having read this page I having built a couple of tables and a trigger. The idea is that when an INSERT, UPDATE or DELETE is performed on the first table Matt the data operated upon will be inserted into the second, audit, table MattAudit. The…
Matt W
  • 11,753
  • 25
  • 118
  • 215
1
vote
1 answer

Track the changes of a few columns in an existing table leveraging primary keys?

I'm currently trying to track the changes of a few columns (let's call them col1 & col2) in a SQL Server table. The table is not being "updated/inserted/deleted" over time; new records are just being added to it (please see below 10/01 vs 11/01).…
Jeremie
  • 65
  • 1
  • 7
1
vote
2 answers

SQL: How to best query a history table to create a snapshot overview of a specific date

I am relatively new to SQL and database structures and have a question about what the best way is to go about this. I have historic change data of objects that will occasionally need to be transformed to snapshots of specific dates. Example of the…
Mark
  • 75
  • 1
  • 7
1
2
3 4 5