New answers tagged

0

AUDIT_TRAIL If you want the SQL text to be captured, you need to use the extended option for audit_trail (db, extended or xml, extended). alter system set AUDIT_TRAIL=db, extended scope=spfile; or alter system set AUDIT_TRAIL=xml, extended scope=spfile; Changing this setting requires a database restart to take effect. For FGA you can specify a ...


1

In Oracle 12c Mixed Mode is enabled by default which makes the use familiar with Unified Auditing prior to switching to full Unified Auditing. Mixed Mode allows to pre-12c audit functionality to co-exist with new Unified Auditing functionality. Using Mixed Mode audit data can be found both in the traditional locations as well as in SYS.UNIFIED_AUDIT_TRAIL. ...


0

I created a simple test rig to trial SQL Server Audit against triggers, and potentially other options. In my tests of inserting 1 million rows into a table I got 52, 67 and 159 seconds for baseline, SQL Audit and my trigger respectively: Now this isn't particularly scientific but does potentially offer you one way of comparing approaches. Have a look ...


2

Try this, which slightly modifies your WHERE clause to correctly follow the SQL Server operator precedence rules: CREATE SERVER AUDIT [PCIAudit] TO FILE ( FILEPATH = N'D:\SQLAuditLogs\' , MAXSIZE = 2048 MB , RESERVE_DISK_SPACE = OFF ) WITH ( QUEUE_DELAY = 0 , ON_FAILURE = CONTINUE ) WHERE ([database_name] = 'DataBase1' OR [database_name] = ...


2

One benefit of the Audit that comes to mind is that it will automatically record who turns it on and off, XE won't do that out of the box (though you might find an event that tracks XE stop/start). You may also find that the two capture different data, depending on exactly what you want. Regarding doing some testing, you would need to have a database ...


0

You have tradeoffs to consider when deciding on the design. Which do you want to optimize for: Insert new item Revise item Fetch "current" item Fetch some old revision Fetch all revisions If one of those actions dominates, then design for it, and kludge for the rest. Also, do revisions need extra columns, such as revisor, revision_date, ...


-1

I have spent a few days to come up with a Stored Procedure to automatically/dynamically create UPDATE / DELETE triggers in MariaDB (Works with v 10.1.9) auditing all changes on updates and deletions. The solution uses the INFORMATION_SCHEMA to automatically build an audit trigger for each of your tables. On Update only changed columns are audited, whilst on ...


2

Temporal tables are new and in all editions. They add a start and end time to rows and keeps a history of datachanges with some DDL to pull back changes. It may be possible to stick into existing applications but has some limitations (eg no cascade deletes) and so is much more likely to be put in during design time so that developers don't need to roll ...


2

If I write it out like this @OldValue = (select deleted.ID from #del) I get the correct value. You only get the correct value if there is a single row being updated or deleted. But Triggers in SQL Server are fired once per DML operation, and the inserted and deleted pseudo-tables contain all of the rows per that operation. So if there were 2 or more ...


1

Yes, you can do this inside a trigger. A trigger can reference the objects INSERTED and DELETED which can be used as tables to tell what information is inserted and deleted. When an update is made, the old rows are "deleted" and the new rows are "inserted." Write your SQL statement to INSERT all the rows from INSERTED and DELETED into your log table. I ...


0

I'm short on time so I wasn't able review your code, but I'm basing this answer off your question: How to get the deleted values/capturing changes? Just yesterday I was reading from CH. 8 of Itzik Ben-Gan's "Microsoft SQL Server 2012 T-SQL Fundamentals" and he had a good section on creating audit tables with the use of the OUTPUT clause. To me this was eye ...


1

No, it is not. Data that is gone is gone. This type of auditing is normally implemented by custom code (triggers writing to an audit table or something like this).



Top 50 recent answers are included