Question

23. Consider a trigger which archives deleted rows from a table into a separate archive table.

a. Is using a trigger to achieve this using needless computation power?

b. What is another way of implementing this feature without using triggers?

c. What are the arguments in favour of this solution?

d. What are the arguments against this solution?

23. Consider a trigger which archives deleted rows from a table into a separate archive table.

a. Is using a trigger to achieve this using needless computation power?

b. What is another way of implementing this feature without using triggers?

c. What are the arguments in favour of this solution?

d. What are the arguments against this solution?

Answer 1

a)

AFTER DELETE trigger SQL Server

The SQL Server AFTER Delete Triggers will fire after the completion of Delete operation on a table. For this SQL Server AFTER Delete Triggers demonstration, We use the below-shown tables. Here, our task is to create SQL Server AFTER DELETE TRIGGER on this Employee table. And by using this SQL Server AFTER Delete Trigger we want to Insert the deleted records from the Employee table into the Employee Audit table.

SQL Server initiates an AFTER DELETE trigger whenever a delete statement event occurs. You can download the AFTER DELETE Trigger script used here so that you can run this script on your local SQL Server, while following the problem below. We want to keep track of records deleted from employee table into employee_history table.

MySQL AFTER DELETE triggers are automatically invoked after a delete event occurs on the table. Here is the basic syntax of creating a MySQL AFTER DELETE trigger: CREATE TRIGGER trigger_name AFTER DELETE ON table_name FOR EACH ROW trigger_body;

AFTER DELETE trigger in MySQL

MySQL AFTER DELETE Trigger,

MySQL AFTER DELETE triggers​​

CREATE TRIGGER trigger_name AFTER DELETE ON table_name FOR EACH ROW trigger_body; In this syntax: First, specify the name of the trigger that you want to create in the CREATE TRIGGER clause. Second, use AFTER DELETE clause to specify the time to invoke the trigger. Here is the basic syntax of creating a MySQL AFTER DELETE trigger: CREATE TRIGGER trigger_name AFTER DELETE ON table_name FOR EACH ROW trigger_body; In this syntax: First, specify the name of the trigger that you want to create in the CREATE TRIGGER clause. Second, use AFTER DELETE clause to specify the time to invoke the trigger.

b) using xml

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.

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.

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.

-- 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';

c)

An XML Solution

summarizes the archiving requirements in five main points:

1. The data must be archived based on condition
2. The archived data must be dropped from the main database without much performance impact.
3. Reinstating the archived data must be quick and easy.
4. The solution must allow for table structures to change.
5. It must be possible to search inside the archived data without restoring it to the main database.

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

d)

• Triggers are easy to code. The fact that they are coded like stored procedures which makes getting started with triggers easy.
• Triggers allow you to create basic auditing. By using the deleted table inside a trigger you can build a decent audit solution that inserts the contents of the deleted table data into an audit table which holds the data that is either being removed by a DELETE statement or being changed by an UPDATE statement.
• You can call stored procedures and functions from inside a trigger.
• Triggers are useful when you need to validate inserted or updated data in batches instead of row by row. Think about it, in a trigger's code you have the inserted and deleted tables that hold a copy of the data that potentially will be stored in the table (the inserted table); and the data that will be removed from the table (the deleted table).
• You can use triggers to implement referential integrity across databases. Unfortunately SQL Server doesn't allow the creation of constraints between objects on different databases, but by using triggers you can simulate the behavior of constraints. I recently wrote a tip about this matter which you can read here SQL Server Referential Integrity Across Databases Using Triggers.
• Triggers are useful if you need to be sure that certain events always happen when data is inserted, updated or deleted. This is the case when you have to deal with complex default values of columns, or modify the data of other tables.
• You can use external code as a trigger by using CLR triggers. This type of trigger specifies the method of an assembly written in .NET to bind with the trigger.
• Triggers can be nested to up to 32 levels. A trigger is considered to be nested when it performs an action that fires another trigger.
• Triggers allow recursion. Triggers are recursive when a trigger on a table performs an action on the base table that causes another instance of the trigger to fire. This is useful when you have to solve a self-referencing relation