Tag Info

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 ...


20

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 ...


13

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 ...


10

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

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 ...


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 ...


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

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 ...


5

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. ...


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

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 ...


4

The solution we use internally for this matter is to have a status column with some hard coded values for some specific states of the object: Deleted, Active, Inactive, Open, Closed, Blocked - each status with some meaning used in the application. From db point of view we don't remove objects, we just change the status and keep history for each change in the ...


4

I do agree with DeCosta's answer. What requirements, specifications are you going to be audited on? But, as my best shot in the dark: This is a "best practice" publication for security related to SQL 2005 that was published by Microsoft http://download.microsoft.com/download/8/5/e/85eea4fa-b3bb-4426-97d0-7f7151b2011c/SQL2005SecBestPract.doc And the ...


4

I applaud your effort, however the question asked is probably too vague. Different rules apply to different industries and furthermore, sometimes you have the opportunity to set your own rules, you just need to follow them. I would suggest checking with someone as to what audit you are expected to pass, then finding the specific requirements.


4

Overall, triggers have a silent and hidden impact on your data modification performance. I have seen environments where cascading errors were caused by updating other tables from within a trigger and the updated tables had triggers. As far as auditting is concerned, a trigger may be used, but if you have the Enterprise edition of SQL Server 2008, or ...


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

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 ...


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

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 ...


4

SQL Server 2008 (r1) introduced Extended Events and with that came SQL Server Audit and works similar to XEvents to audit events. So it is less intrusive than C2 Auditing and server side traces. It is very granular and fairly easy to setup. This feature is available in all editions of SQL Server 2008 R2. A good write-up intro/how-to can be found here by ...


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.


3

If you're using SQL Server 2008 Enterprise Edition or newer, you may also consider using Change Data Capture, which: has a lower impact on the system than triggers; covers the scenarios you mentioned; the auditing logic is proven built in into SQL Server (instead of triggers, which you would have to implement yourself).


3

I have just the situation described above by Mark Triggers are often the only option when you're enhancing a 3rd party solution and the extension points are not exposed by the systems API. My example: a c# application written by another developer using LLBL.gen as the ORM. Any change to the data structure that you want the C# code to use implies the ...


3

In Oracle triggers should be avoided in most situations because they divide a portion of the logic into a separate code area that is harder to debug and only runs as side effect, not directly. Legitimate exceptions include instead of triggers, integrating with third party code, and setting complex default values. The latter may be the biggest argument ...


3

To add to the excellent links above; I found the following documents as helpful reference regarding security and auditing: PCI White paper Deploying SQL Server 2008 Based on Payment Card Industry Data Security Standards (PCI DSS) : Link HIPPA white paper: Link Microsoft SQL Server security bench mark from Center for Internet Security. Link Also google for ...



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