Hot answers tagged

29

Yeah, I would definitely go for the second option, but I would add one more field a date field. So you add : delete boolean delete_date timestamp It would let you give a time for the undelete action. If time is less than an hour one can undelete. To really delete the entry deleted just create a store procedure that will clean every entry with ...


22

I've not tried fn_dblog on Express but if it is available the following will give you delete operations: SELECT * FROM fn_dblog(NULL, NULL) WHERE Operation = 'LOP_DELETE_ROWS' Take the transaction ID for transactions you're interested in and identify the SID that initiated the transaction with: SELECT [Transaction SID] FROM ...


20

In our applications we don't really delete anything at a users request anyway (our clients are in regulated environments where deleting anything can potentially lead to legal issues). We keep the older versions in a separate audit table (so for the table some_table where is also a table called some_table_audit) which is identical apart from having an ...


18

One other important aspect of audit tables that hasn't been highlighted thus far, is that in addition to keeping track of who did what to which record (often including before and after snapshots) audit tables are write-once. Records in an audit table may not be updated or deleted (see note), only inserted. This is sometimes imposed using triggers or ...


15

Audit Tables are used to track transactions against a particular table or tables. They allow you to see an ongoing "log" (for lack of a better word). For instance, say you have a table like this: create table SensitiveInformation ( SensitiveNumber int not null, SensitiveData varchar(100) not null ) go There may be users and/or applications that ...


10

As you've identified, storing the price on the order makes the technical implementation easier. There are a number of business reasons why this may be beneficial though. In addition to web transactions, many businesses support sales through other channels, e.g.: Over the phone Sales agents "on the road" At a physical location (e.g. shop, office) In ...


9

With a boolean deleted column , you'll start to have problems if you're table start's to grow and get's realy big . I suggest you move deleted columns once a week ( more or less depending on you're specs ) to a different table . That way you have a nice small active table and a big one containing all records gathered over time .


9

Personally, I would use triggers for auditing. If they fail, then the main DML gets rolled back (same) The main update requires the DML to succeed A table will typically have several DML paths You may not be able to use code You will have direct table update that bypass code at some point Ok, so we'll use triggers. Some notes Ensure they work for ...


7

Most likely you did. Most cases database files, including transaction log, are configured for auto-growth. Auth-growth enables the file to increase its size as needed, any time is needed. The trigger for growth can be any activity that requires log space. The activity that triggered the growth is most cases ordinary, normal activity. The true problem is why ...


7

I'd go with the separate table. Ruby on Rails has an acts_as_versioned plugin, which basically saves a row to another table with the postfix _version before it updates it. While you don't need that exact behavior, it should also work for your case (copy before deleting). Like @Spredzy I'd also recommend adding a delete_date column to be able to ...


7

Don't bother tracking audit data on a per-field basis. It's much easier to have an audit table with the same structure as the main table and timestamp and user information tracked on the table. This architecture has a few advantages: You can easily create a view across the main table and audit table to show a complete history includint current versions. ...


7

Sounds like we hold similar views on trigger usage but I'm very interested to hear the opposite opinions from other DBAs. Personally, I view them as a last resort and have only implemented triggers where there is no other option. The exception being INSTEAD OF triggers against views. Triggers are often the only option when you're enhancing a 3rd party ...


6

There are many requirements for capturing changed data. I list only three common ones below. Audits (HIPAA, SAS70, etc.)...one may need to audit SQL Server logins as well as attribute data changes to specific users EDI...some insurance carriers require one to send to them current and previous data when a policy is altered, say, due to a life event. One may ...


6

You would probably want to use the general query log. The general query log is a general record of what mysqld is doing. The server writes information to this log when clients connect or disconnect, and it logs each SQL statement received from clients. One important thing with logging for security is that an attacker cannot access the log to ...


6

I would suggest you to create a server side trace or enable SQL Audit to track down activity from users that you dont trust. Remember that DMV data gets reset if the DMV is cleared out, sql server is restarted, etc. The closest you can get is using below query: /****************************************************** Script : Findout Who did what ? Author ...


5

SQL Server Audit leverages the new Extended Events architecture introduced in 2008. If you look at the following DMVs, you can see the related structures appear when the audit is started. select * from sys.dm_server_audit_status select * from sys.dm_xe_session_events select * from sys.dm_xe_session_targets Note that audits have their own set of special ...


5

Run this in the context of the master database as you must be running it under the context of a user database set to SQL Server 2000 compatibility level. The following should work fine USE master; SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query] FROM sys.dm_exec_query_stats AS deqs CROSS APPLY ...


5

You could do that with DDL triggers ([before|after] grant or revoke). The docs are in the Oracle® Database PL/SQL Language Reference. You should look carefully at the event attribute functions table, and the Event Attribute Functions for Client Event Triggers section to know what information is available in those contexts. Here's an example trigger ...


5

If you didn't enable auditing in your database, you usually cannot spot who and when queried and/or modified the database, at least as easily as with auditing enabled. You can use various types of auditing for these tasks: Standard auditing Fine-Grained Auditing (FGA) Triggers With standard auditing, you can audit object and system privileges, such as ...


5

I would probably go with triggers. You will be warned about performance implications and all that, but truthfully there isn't a "free" way to audit, and any option you try will have some impact on your workload. You will need to be aware that if someone updates the whole table, you will be logging that too, so be prepared for your auditing data growth to ...


4

The closest match to what you're looking for seems to be the SQL Server Audit feature, which was added in SQL Server 2008 as a replacement/successor for SQL Trace. I recommend following the link to read up on it, since its setup/use is too big a topic to describe here in any meaningful way.


4

Auditing has been more improved in SQL Server 2008 and up. There are many ways to trace a SELECT .... (make sure you are not doing it against an HR database :-)) Option 1: Through SQL trace, where in depending on what trace events have been selected, you can get logged/audited. Option 2: Under database --> Security --> Database Audit Specifications . Here ...


4

If you only need to track database access, maybe collecting schema stability locks with extended events is enough. I blogged this idea last week for a totally different reason (tracking unused objects) but the script can be adapted to fit your needs. You can find it here. Basically, it reads an extended events session using the streaming api and then ...


4

I am not sure about CDC, but if the login has view server state permission you can use DMVs to get some information. This is given in Books Online here. I changed the query to add columns which would give you the IP address: SELECT c.session_id, c.net_transport, c.encrypt_option, c.auth_scheme, s.host_name, s.program_name, ...


4

With SQLAudit you cannot track HostName or IP address. However, there's an alternative method for auditing, based on streaming extended events. I blogged about it earlier this year. Basically, you treat locks acquired as indicators of the operations being performed on the tables (shared locks and schema stability locks for reads and exclusive locks for ...


4

While using EXECUTE AS User = @UserId may be your best option (depending on other issues), here is an alternative approach: In you stored procedures, or at anytime in your SQL session before you do the DELETE execute the following command: SET CONTEXT_INFO @UserId Then in your Trigger you can retrieve this value with SELECT @var = ...


4

For the IP address you can use the function inet_client_addr() All the system information functions are documented in the manual in the chapter "System Information Functions" http://www.postgresql.org/docs/current/static/functions-info.html


4

You can obtain most of the info from your screen shot from xp_msver. It goes back as far as SQL 2005. The other information might be in sys.configurations: http://msdn.microsoft.com/en-us/library/ms188345.aspx


4

fn_dblog is the way to look backwards as the other commentators have said. Only allowing the users to modify the data through stored procedures is a great way to prevent this happening in the first place, as you can add logic to prevent users from mass modifying records they shouldn't, or ensuring that they have to provide correct values for updates. This ...


4

I'd probably stick with triggers, for the same reasons gbn described, but if performance becomes an issue, you may want to consider using Service Broker to perform the work asynchronously (if business requirements will allow that). The typical design would be to have a trigger call a stored procedure that sends a Service Broker message, and the Service ...



Only top voted, non community-wiki answers of a minimum length are eligible