Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>We chose separate tables as it will keep your primary table lean and faster. We also opted for triggers, reasoning that if you ever change the entry mechanism, you wouldn't want to have to rewrite your auditing. Also it can capture accidental DBA side changes.</p> <p>Since update is effectively a delete and then an insert, you can achieve what has been suggested with a single trigger- this is what we did.</p> <ul> <li><p>Create a table exactly matching your existing table, but with some added columns: AUDIT_GUID VARCHAR(40), AUDIT_TIMESTAMP DATETIME, AUDIT_ACTION VARCHAR(20)</p></li> <li><p>Create an "AFTER INSERT,DELETE,UPDATE" Trigger using the following general pattern (simply add more columns where necessary).</p> <pre><code>CREATE TRIGGER CustomerAudit ON Customer AFTER INSERT,DELETE,UPDATE AS BEGIN IF (TRIGGER_NESTLEVEL()&gt;1) RETURN DECLARE @Time DateTime = CURRENT_TIMESTAMP DECLARE @Audit_GUID varchar(100) = NEWID() INSERT INTO Customer_History (FirstName, LastName, Audit_Date, Audit_Action, Audit_GUID) SELECT FirstName, LastName, @Time, 'Delete', @Audit_GUID FROM Deleted INSERT INTO Customer_History (FirstName, LastName, Audit_Date, Audit_Action, Audit_GUID) SELECT FirstName, LastName, @Time, 'Insert', @Audit_GUID FROM Inserted END </code></pre></li> </ul> <p>If you want to find updates, they will be rows in the history table that have a delete &amp; update with the same Audit_GUID value. The timestamp also allows you to check changes made at a certain time, and we've also added a currentuser to find the person to blame if necessary!</p>
    singulars
    1. This table or related slice is empty.
    1. POBest Solution to Create a change History for several SQL Tables
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USRunonthespot
    1. USRunonthespot
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POBest Solution to Create a change History for several SQL Tables
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. CO(If anyone can suggest how I can get StackOverflow to format this code properly, please let me know)
      singulars
      1. PO
      1. USRunonthespot
    2. CO@Runonthespot: Code following a bullet needs 8 leading spaces to format right. I also added a space after commas in the column lists, which seems to help "unwrap" long lines.
      singulars
      1. PO
      1. USMike Sherrill 'Cat Recall'
    3. CO@Catcall, many thanks.
      singulars
      1. PO
      1. USRunonthespot
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload