Although I’m not generally a big proponent of storing XML in a SQL database, I feel that the archiving of deleted data that relates to a single business transaction is one of the rare cases where it is reasonable option.
Some Sample Data
Let’s start by using Data Definition Language (DDL) to create a couple of example tables, and use Data Manipulation Language (DMS) to populate those tables. These are borrowed from a previous article (I love reusing code). The data model is an Invoices table with child, detail records stored in an Invoice_Details table.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
CREATE TABLE dbo.Invoices ( InvoiceNo VARCHAR(20) NOT NULL ,InvoiceDate DATE NOT NULL ,CustomerID VARCHAR(20) NOT NULL ,CustomerName VARCHAR(100) NOT NULL ,CustomerAddr1 VARCHAR(100) NOT NULL ,CustomerAddr2 VARCHAR(100) NULL ,CustomerZipcode VARCHAR(10) NOT NULL ,PRIMARY KEY (InvoiceNo) ); -- Seed the Invoices table INSERT INTO dbo.Invoices ( InvoiceNo, InvoiceDate, CustomerID, CustomerName ,CustomerAddr1, CustomerAddr2, CustomerZipcode ) SELECT 'IN20141000001', '2014-11-03', '20001', 'ACME MINERALS', '35 WESTCHESTER ST' ,'CAIRNS, AU', '4810' UNION ALL SELECT 'IN20141000002', '2014-11-04', '20002', 'ACE CHEMICALS', '201 BROADWAY AVE' ,'CAIRNS, AU', '4810'; CREATE TABLE dbo.Invoice_Details ( InvoiceNo VARCHAR(20) NOT NULL ,LineItem BIGINT IDENTITY ,ItemNo VARCHAR(20) NOT NULL ,Quantity INT NOT NULL ,Price MONEY NOT NULL ,ExtendedPrice AS (Quantity * Price) ,PRIMARY KEY (InvoiceNo, LineItem) ,CONSTRAINT invd_fk1 FOREIGN KEY (InvoiceNo) REFERENCES dbo.Invoices (InvoiceNo) ); -- Seed the Invoice Details table INSERT INTO dbo.Invoice_Details (InvoiceNo, ItemNo, Quantity, Price) SELECT 'IN20141000001', '1000055', 12, 233.22 UNION ALL SELECT 'IN20141000001', '1000056', 3, 189.33 UNION ALL SELECT 'IN20141000002', '1000055', 1, 222.33 UNION ALL SELECT 'IN20141000002', '1000056', 4, 123.33; SELECT * FROM dbo.Invoices; SELECT * FROM dbo.Invoice_Details; --GO --DROP TABLE dbo.Invoice_Details; --DROP TABLE dbo.Invoices; |
In this sample, we have a hierarchical structure of two tables with a parent-child relationship between them, implemented using the FOREIGN KEY constraint. This is a simplification; in the real world there could be many tables originating from that single parent. In the particular case where I used this, there were a total of nine tables hanging off that same parent and the hierarchy was four levels deep.
The DROPs are provided so you can clean up your sandbox later.
Deleting the Data with Archiving
The only reason you’d ever need to archive deleted data is on the off-chance that someone deletes a record that shouldn’t have been deleted, and then they want it to be restored. There are alternative, traditional ways to do this:
- Create “shadow” archiving tables into which you simply move the deleted transactions. This can become rather cumbersome as the number of tables participating in the relationship grows large, cluttering up your data model.
- Introduce flag column(s), for example the user that did the delete and a date/time stamp, in each table to represent a “soft-delete.” In my experience, this can be rather a nuisance because it requires programming all over the place to ensure that deleted transactions are excluded from query results. You could be a bit more clever and use indexed VIEWs to achieve excluding the deleted rows, but you may still run the risk of later developers not realizing why these VIEWs need to be used.
- Conceivably you could dump the deleted transactions as some sort of text-based file (CSV or XML) or even into an Excel spreadsheet residing somewhere on your database server, but that sounds like an awful lot of complexity to me.
Let’s now look at how we can use XML to keep our deleted data around for future restoration or inspection.
Use One Table to Store the Entire Parent to Great-great-great-grandchild Relationships
Here is some more DDL to define a table we’ll use to archive deleted transactions.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE TABLE dbo.Deleted_Invoices ( -- The PRIMARY KEY of the transaction in the parent table that was deleted InvoiceNo VARCHAR(20) NOT NULL -- Who deleted the transaction and when ,UserID VARCHAR(20) NOT NULL ,DeletedDT DATETIME NOT NULL -- One column for each table in the hierarchy ,Invoices XML NULL ,Invoice_Details XML NULL ,PRIMARY KEY (InvoiceNo, UserID, DeletedDT) ); GO -- DROP TABLE dbo.Deleted_Invoices; |
As you can see there is one XML column for each of the tables participating in our transaction’s hierarchy. There are also a couple of leading columns to capture the:
- PRIMARY KEY of the deleted transaction (InvoiceNo).
- User ID and date/time that the transaction was deleted.
To Delete/Archive a Transaction
Within a transaction, we can first copy off the invoice and details to our archiving table, then do a cascade delete of the invoice from our tables. The code below is best put into a Stored Procedure rather than constructed within your application’s front end.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 |
CREATE PROCEDURE dbo.Delete_InvoiceParent ( @UserID VARCHAR(20) ,@InvoiceNo VARCHAR(20) ) AS BEGIN; SET NOCOUNT ON; SET XACT_ABORT ON; DECLARE @Error_Code INT = 0 BEGIN TRANSACTION T1; BEGIN TRY; -- Archive the invoices we'll delete INSERT INTO dbo.Deleted_Invoices ( InvoiceNo, UserID, DeletedDT, Invoices, Invoice_Details ) SELECT @InvoiceNo, @UserID, GETDATE() ,( -- The parent transaction record SELECT InvoiceNo, InvoiceDate, CustomerID, CustomerName ,CustomerAddr1, CustomerAddr2, CustomerZipcode FROM dbo.Invoices WHERE InvoiceNo = @InvoiceNo FOR XML PATH('Invoices') ) ,( -- The child transaction records (details) SELECT InvoiceNo, LineItem, ItemNo, Quantity, Price FROM dbo.Invoice_Details WHERE InvoiceNo = @InvoiceNo FOR XML PATH('Invoice_Details') ); -- Cascade delete details first DELETE FROM dbo.Invoice_Details WHERE InvoiceNo = @InvoiceNo; DELETE FROM dbo.Invoices WHERE InvoiceNo = @InvoiceNo; END TRY BEGIN CATCH; SELECT @Error_Code = ERROR_NUMBER(); -- Useful information for debugging PRINT 'ERROR_NUMBER() '+ CAST(@Error_Code AS VARCHAR(10)); PRINT 'ERROR_SEVERITY() '+ CAST(ERROR_SEVERITY() AS VARCHAR(10)); PRINT 'ERROR_STATE() '+ CAST(ERROR_STATE() AS VARCHAR(10)); PRINT 'ERROR_PROCEDURE() '+CAST(ERROR_PROCEDURE() AS VARCHAR(8000)); PRINT 'ERROR_LINE() '+ CAST(ERROR_LINE() AS VARCHAR(100)); PRINT 'ERROR_MESSAGE() '+ CAST(ERROR_MESSAGE() AS VARCHAR(8000)); PRINT 'XACT_STATE() '+ CAST(XACT_STATE() AS VARCHAR(5)); END CATCH; IF @Error_Code <> 0 OR XACT_STATE() = -1 ROLLBACK TRANSACTION T1; ELSE IF @Error_Code = 0 AND XACT_STATE() = 1 COMMIT TRANSACTION T1; END |
You can now run the following to archive/cascade delete the records.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
-- A cascade delete that works EXEC dbo.Delete_InvoiceParent @UserID = 'DCAMPS' ,@InvoiceNo = 'IN20141000002'; -- This will cause an error when the SP is run -- and is why we set XACT_ABORT in the SP (to -- clean-up the transaction) ALTER TABLE dbo.Invoices DROP COLUMN CustomerZipcode; GO -- A cascade delete that fails EXEC dbo.Delete_InvoiceParent @UserID = 'DCAMPS' ,@InvoiceNo = 'IN20141000001'; |
We did not include the computed column (ExtendedPrice of our Invoice_Details table) into our XML tables because it will not be needed when we restore this archived transaction. You’ll need to sequence the DELETEs so that the tables that are the deepest in the hierarchy are deleted first.
Note that you should determine if there are any DML TRIGGERs that run on DELETE and examine what they do, then decide whether to ENABLE/DISABLE them prior to running the script above. For example, the Invoice_Details might have an audit trail being created when invoice details are inserted, updated or deleted.
At this time, you’ll want to rerun the initial code that sets up the Invoices and Invoices_Details tables (drop them first) to repopulate the tables, then rerun the first SP EXEC statement above.
Using a Trigger
This same thing can be done with an INSTEAD OF TRIGGER, although the only thing you cannot do is record the user that performed the DELETE. Here is that TRIGGER code:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 |
CREATE TRIGGER dbo.Invoice_Delete ON dbo.Invoices INSTEAD OF DELETE AS BEGIN SET NOCOUNT ON; SET XACT_ABORT ON; -- Insert statements for trigger here BEGIN TRANSACTION T1; BEGIN TRY; -- Archive the invoices we'll delete INSERT INTO dbo.Deleted_Invoices ( InvoiceNo, UserID, DeletedDT, Invoices, Invoice_Details ) SELECT InvoiceNo, '', GETDATE() ,( -- The parent transaction record SELECT InvoiceNo, InvoiceDate, CustomerID, CustomerName ,CustomerAddr1, CustomerAddr2, CustomerZipcode FROM dbo.Invoices b WHERE a.InvoiceNo = b.InvoiceNo FOR XML PATH('Invoices') ) ,( -- The child transaction records (details) SELECT InvoiceNo, LineItem, ItemNo, Quantity, Price FROM dbo.Invoice_Details b WHERE a.InvoiceNo = b.InvoiceNo FOR XML PATH('Invoice_Details') ) FROM DELETED a; -- Cascade delete details first DELETE a FROM dbo.Invoice_Details a JOIN DELETED b ON a.InvoiceNo = b.InvoiceNo; DELETE a FROM dbo.Invoices a JOIN DELETED b ON a.InvoiceNo = b.InvoiceNo; END TRY BEGIN CATCH; -- Useful information for debugging PRINT 'ERROR_NUMBER() '+ CAST(ERROR_NUMBER() AS VARCHAR(10)); PRINT 'ERROR_SEVERITY() '+ CAST(ERROR_SEVERITY() AS VARCHAR(10)); PRINT 'ERROR_STATE() '+ CAST(ERROR_STATE() AS VARCHAR(10)); PRINT 'ERROR_PROCEDURE() '+CAST(ERROR_PROCEDURE() AS VARCHAR(8000)); PRINT 'ERROR_LINE() '+ CAST(ERROR_LINE() AS VARCHAR(100)); PRINT 'ERROR_MESSAGE() '+ CAST(ERROR_MESSAGE() AS VARCHAR(8000)); PRINT 'XACT_STATE() '+ CAST(XACT_STATE() AS VARCHAR(5)); END CATCH; IF XACT_STATE() = -1 ROLLBACK TRANSACTION T1; ELSE IF XACT_STATE() = 1 COMMIT TRANSACTION T1; END |
Note how we’ve set the UserID column in the Deleted_Invoices table to blank because we don’t have access to that information in the TRIGGER (although you could substitute it with the SQL Server login account if that is applicable).
Assuming you’ve restored the original data to the two invoices tables, you can run the following code to delete and archive invoice IN20141000002 the same as before.
|
1 2 3 4 5 6 7 8 9 10 11 |
DELETE FROM dbo.Invoices WHERE InvoiceNo = 'IN20141000002'; SELECT * FROM dbo.Invoices; SELECT * FROM dbo.Invoice_Details; SELECT * FROM dbo.Deleted_Invoices; |
The XML that our Archiving Process Created
The XML that was created in the prior step is element-based. We could have just as well chosen attribute-based XML. Since element-based XML tends to be a little more verbose it will take up marginally more storage.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
<Invoices> <InvoiceNo>IN20141000002</InvoiceNo> <InvoiceDate>2014-11-04</InvoiceDate> <CustomerID>20002</CustomerID> <CustomerName>ACE CHEMICALS</CustomerName> <CustomerAddr1>201 BROADWAY AVE</CustomerAddr1> <CustomerAddr2>CAIRNS, AU</CustomerAddr2> <CustomerZipcode>4810</CustomerZipcode> </Invoices> <Invoice_Details> <InvoiceNo>IN20141000002</InvoiceNo> <LineItem>3</LineItem> <ItemNo>1000055</ItemNo> <Quantity>1</Quantity> <Price>222.3300</Price> </Invoice_Details> <Invoice_Details> <InvoiceNo>IN20141000002</InvoiceNo> <LineItem>4</LineItem> <ItemNo>1000056</ItemNo> <Quantity>4</Quantity> <Price>123.3300</Price> </Invoice_Details> |
Restore a Transaction
When you need to restore a transaction, you can parse the XML back into the original tables. Once again we’ll put that code into a SP.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 |
CREATE PROCEDURE dbo.Restore_Invoice ( @UserID VARCHAR(20) ,@InvoiceNo VARCHAR(20) ) AS BEGIN; SET NOCOUNT ON; SET XACT_ABORT ON; DECLARE @Error_Code INT = 0; BEGIN TRANSACTION T1; BEGIN TRY; -- Restore the invoice header first INSERT INTO dbo.Invoices ( InvoiceNo, InvoiceDate, CustomerID, CustomerName ,CustomerAddr1, CustomerAddr2, CustomerZipcode ) SELECT InvoiceNo = b.Invoices.value('InvoiceNo[1]', 'VARCHAR(20)') ,InvoiceDate = b.Invoices.value('InvoiceDate[1]', 'DATE') ,CustomerID = b.Invoices.value('CustomerID[1]', 'VARCHAR(20)') ,CustomerName = b.Invoices.value('CustomerName[1]', 'VARCHAR(100)') ,CustomerAddr1 = b.Invoices.value('CustomerAddr1[1]', 'VARCHAR(100)') ,CustomerAddr2 = b.Invoices.value('CustomerAddr2[1]', 'VARCHAR(100)') ,CustomerZipcode = b.Invoices.value('CustomerZipcode[1]', 'VARCHAR(10)') FROM dbo.Deleted_Invoices a CROSS APPLY Invoices.nodes('/Invoices') b (Invoices) WHERE a.InvoiceNo = @InvoiceNo; SET IDENTITY_INSERT dbo.Invoice_Details ON; -- Restore the invoice details (child table) next INSERT INTO dbo.Invoice_Details ( InvoiceNo, LineItem, ItemNo, Quantity, Price ) SELECT InvoiceNo = b.Invoice_Details.value('InvoiceNo[1]', 'VARCHAR(20)') ,LineItem = b.Invoice_Details.value('LineItem[1]', 'BIGINT') ,ItemNo = b.Invoice_Details.value('ItemNo[1]', 'VARCHAR(20)') ,Quantity = b.Invoice_Details.value('Quantity[1]', 'INT') ,Price = b.Invoice_Details.value('Price[1]', 'MONEY') FROM dbo.Deleted_Invoices a CROSS APPLY Invoice_Details.nodes('/Invoice_Details') b (Invoice_Details) WHERE a.InvoiceNo = @InvoiceNo; -- Remove the deleted transaction from the archives table DELETE FROM dbo.Deleted_Invoices WHERE InvoiceNo = @InvoiceNo; SET IDENTITY_INSERT dbo.Invoice_Details OFF; END TRY BEGIN CATCH; SELECT @Error_Code = ERROR_NUMBER(); PRINT @InvoiceNo; -- Useful information for debugging PRINT 'ERROR_NUMBER() '+ CAST(@Error_Code AS VARCHAR(10)); PRINT 'ERROR_SEVERITY() '+ CAST(ERROR_SEVERITY() AS VARCHAR(10)); PRINT 'ERROR_STATE() '+ CAST(ERROR_STATE() AS VARCHAR(10)); PRINT 'ERROR_PROCEDURE() '+CAST(ERROR_PROCEDURE() AS VARCHAR(8000)); PRINT 'ERROR_LINE() '+ CAST(ERROR_LINE() AS VARCHAR(100)); PRINT 'ERROR_MESSAGE() '+ CAST(ERROR_MESSAGE() AS VARCHAR(8000)); PRINT 'XACT_STATE() '+ CAST(XACT_STATE() AS VARCHAR(5)); END CATCH; IF @Error_Code <> 0 OR XACT_STATE() = -1 ROLLBACK TRANSACTION T1; ELSE IF @Error_Code = 0 AND XACT_STATE() = 1 COMMIT TRANSACTION T1; END |
We can restore our archived transaction with the following:
|
1 2 3 |
EXEC dbo.Restore_Invoice @UserID = 'DCAMPS' ,@InvoiceNo = 'IN20141000002'; |
Note that because the LineItem is an IDENTITY column, we must use the IDENTITY_INSERT setting to allow us to restore the value corresponding to the invoice detail rows that were originally deleted. You’ll find that you must run the INSERTs in exactly the reverse order for the cascade delete in the prior section. We also deleted the archived transaction, because you’d never want to attempt the restore a second time because you’d get PRIMARY KEY violations on the INSERTs as you’d be attempting to re-insert the same invoice number.
You will also need to be careful to DISABLE/ENABLE any TRIGGER on these tables that runs on INSERT.
The Drawback and How to Mitigate that Effect
Let’s think of our table hierarchy that has been archived as an “archiving object.” There may potentially be many of these objects you need to keep track of in your database. There are a number of operations that can affect your code that creates/restores a transaction from one of these objects:
- Adding a column to any table that is a part of the hierarchy tree.
- Dropping a column from any table that is a part of the hierarchy tree.
- Adding a new child table anywhere within the hierarchy tree.
- Dropping a table that is located somewhere (presumably one of the lowest level children) in the hierarchy tree.
If any of those actions occur (particularly in either of the two cases where something is being added), your code could be “silently” impacted, which is to say you may go on archiving transactions that are now incomplete without your code generating any error messages. That is not the case for all of the examples above (some will generate errors during the archiving process).
To guard against issues being caused by most of these, we can create a DDL trigger that generates a warning message on some specific event types. Assuming of course that you’ve properly defined your table hierarchy using FOREIGN KEY constraints to tie the lot together.
Let’s look at an interesting bit of SQL.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
DECLARE @parent_object_id BIGINT = OBJECT_ID('Invoices', 'U') ,@start_object_id BIGINT = OBJECT_ID('Invoice_Details', 'U'); WITH TraverseTableHierarchy AS ( -- Using the starting object's ID, identify all of its foreign keys SELECT a.name, a.object_id, a.parent_object_id, a.referenced_object_id FROM sys.foreign_keys a JOIN sys.all_objects b ON a.parent_object_id = b.object_id WHERE a.parent_object_id = @start_object_id UNION ALL -- Traverse the hiearchy through the foreign keys and identify the parents SELECT b.name, b.object_id, b.parent_object_id, b.referenced_object_id FROM TraverseTableHierarchy a JOIN sys.foreign_keys b ON b.parent_object_id = a.referenced_object_id JOIN sys.all_objects c ON b.parent_object_id = c.object_id ) SELECT a.name, a.object_id, a.parent_object_id, a.referenced_object_id, b.name FROM TraverseTableHierarchy a JOIN sys.all_objects b ON a.referenced_object_id = b.object_id -- Show a result that only includes the parent object for the hierarchy WHERE a.referenced_object_id = @parent_object_id; |
When we run this SQL, we get something like the following where the object IDs will be different in your database.
|
1 2 |
name object_id parent_object_id referenced_object_id name invd_fk1 1512392457 1448392229 1384392001 Invoices |
This shows us that Invoices (in the rightmost “name” column) is affected by anything modified within the @start_object_id (Invoice_Details). The TraverseTableHierarchy Recursive Common Table Expression (rCTE) actually walks through the table hierarchy (upwards) using FOREIGN KEYs until it reaches the topmost table in your data model. It returns only rows matching the @parent_object_id. You may wish to add an OPTION(MAXRECURSION 0) if for some reason your hierarchy could be more than 100 levels in depth. You may need to modify this code if you have tables with self-referencing FOREIGN KEYS.
We can then construct a DDL trigger that looks for Archived Object structures within our database and displays a warning for the various cases.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 |
CREATE TRIGGER Check_Archiving ON DATABASE FOR CREATE_TABLE, ALTER_TABLE AS SET NOCOUNT ON; DECLARE @ArchivedObjects TABLE ( Archived_Object_Name VARCHAR(100) NOT NULL ,Archived_Object_ID BIGINT NOT NULL ); DECLARE @DependentObjects TABLE ( Name VARCHAR(100) ,ID BIGINT ,Parent_ID BIGINT ,Referenced_ID BIGINT ,Name2 VARCHAR(100) ); -- Include here all of your archiving objects (parent object of each) INSERT INTO @ArchivedObjects (Archived_Object_Name, Archived_Object_ID) SELECT 'Invoices', OBJECT_ID('Invoices', 'U'); WITH TraverseTableHierarchy AS ( -- Using the starting object's ID, identify all of its foreign keys SELECT a.name, a.object_id, a.parent_object_id, a.referenced_object_id FROM sys.foreign_keys a JOIN sys.all_objects b ON a.parent_object_id = b.object_id WHERE a.parent_object_id = OBJECT_ID( EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','NVARCHAR(MAX)') , 'U') UNION ALL -- Traverse the hiearchy through the foreign keys and identify the parents SELECT b.name, b.object_id, b.parent_object_id, b.referenced_object_id FROM TraverseTableHierarchy a JOIN sys.foreign_keys b ON b.parent_object_id = a.referenced_object_id JOIN sys.all_objects c ON b.parent_object_id = c.object_id ) INSERT INTO @DependentObjects SELECT a.name, a.object_id, a.parent_object_id, a.referenced_object_id, b.name FROM TraverseTableHierarchy a JOIN sys.all_objects b ON a.referenced_object_id = b.object_id -- The results include only archived objects for each hierarchy WHERE a.referenced_object_id = (SELECT Archived_Object_ID FROM @ArchivedObjects); DECLARE @ArchivedObject VARCHAR(100) = ( SELECT TOP 1 Name2 FROM @DependentObjects ); IF EXISTS ( SELECT 1 FROM @DependentObjects ) PRINT 'Archived Object Warning - DDL operation impacted archived object: ' + @ArchivedObject; GO |
At the heart of the modifications made to the rCTE is the use of EVENTDATA() to query the SQL Server Event Data Schema and return the name of the object that was modified. The OBJECT_ID() function can then be used to return the ID of the object and use that as the starting point for the upward table hierarchy traversal. We’ve put the top-level object IDs into the @ArchivedObjects table variable to allow for cases where you may have several archiving objects you want to keep track of.
This DATABASE TRIGGER can be located in the Object Explorer window in SQL Server Management Studio (SSMS) by expanding the database > Programmability > Database Triggers.
You can test the DDL trigger with this DDL.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
-- Generates a warning ALTER TABLE dbo.Invoice_Details ADD xxx INT NULL; GO -- Generates a warning ALTER TABLE dbo.Invoice_Details DROP COLUMN xxx; GO -- Generates a warning CREATE TABLE dbo.Invoice_Details_Stuff ( InvoiceNo VARCHAR(20) NOT NULL ,LineItem BIGINT IDENTITY ,[Stuff] INT ,CONSTRAINT xxxx1 FOREIGN KEY (InvoiceNo, LineItem) REFERENCES dbo.Invoice_Details(InvoiceNo, LineItem) ); GO -- Does not generate a warning DROP TABLE dbo.Invoice_Details_Stuff; GO -- Does not generate a warning CREATE TABLE dbo.Invoice_Details_Stuff ( InvoiceNo VARCHAR(20) NOT NULL ,LineItem BIGINT IDENTITY ,[Stuff] INT ); GO -- Does not generate a warning DROP TABLE dbo.Invoice_Details_Stuff; GO |
Comments indicate where a warning is generated. Note that dropping a TABLE from the hierarchy does not generate a warning, and will still not do so if you changed the definition of the DDL trigger to this:
|
1 2 3 |
ALTER TRIGGER Check_Archiving ON DATABASE FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE |
Presumably this is because the FOREIGN KEY relationship has been deleted before the trigger is run. But in that case, your delete/archiving code will probably fail anyway.
The second to last case doesn’t generate a warning until a FOREIGN KEY relationship is established between Invoice_Details_Stuff and Invoice_Details (or any other TABLE within the hierarchy).
To DROP this TRIGGER from your sandbox:
|
1 |
DROP TRIGGER Check_Archiving ON DATABASE; |
Conclusion
In this article we explored an alternative method of archiving deleted business transactions. Which method you use probably depends on you and your preferences. Any method you choose will have some potential drawbacks.
For our method, we have at least provided a path to mitigating the drawback. As long as you remember to take action on the warning that will be generated by DDL changes to your archived table hierarchies, you’ll probably be fine.
We’d love to hear stories about any other methods readers have used to archive deleted transactions.
Load comments