From the course: Complete Guide to Advanced SQL Server

Unlock the full course today

Join today to access over 24,400 courses taught by industry experts.

Using the deleted logical table

Using the deleted logical table

Using the temporary Deleted table, we can set up a trigger that will automatically copy records into an Archive table whenever they are removed from the main table. To see how this works, we're going to create two tables, one called Vendors and a second one called VendorArchive. Both of these tables will be almost identical. The Vendors table will have a column for VendorID, that will be the primary key for the table, and a second column to store the vendor name. The VendorArchive table will have the same primary key and VendorName columns, as well as a third column called DateArchived. This will allow us to store a timestamp so that we can know exactly when a record was moved from the Vendors table into the Archive. So the idea is that we would normally use the Vendors table for our day-to-day operations. If it ever comes a time where we want to delete a vendor, the trigger will step in. And instead of processing the delete from the Vendors table, it'll actually move the record into…

Contents