SQLTeam.com | Weblogs | Forums

How to capture deleted records from three related tables and insert into History table?

Greetings again,

Hopefully, I can indulge your assistance one more time. Thanks a lot in advance.

I have the following trigger that captures any record deleted in Speakers table and inserts that record into record SpeakersHistory table. This includes the SpeakerID which is the primary key for the Speakers table.

CREATE TRIGGER SpeakerRecsAfterDelete
ON Speakers
FOR DELETE
AS
 INSERT INTO SpeakerHistory
     (
      speakerID, 
      SpeakerName,
      MinistryName,
      ClientName, 
      DateAdded, 
      Email, 
      WebsiteURL,
      ServerName,
      ServerInstanceName,
      dateDeleted
      )
    SELECT 
      speakerID, 
      SpeakerName, 
      MinistryName,
      ClientName, 
      DateAdded, 
      Email, 
      WebsiteURL,
      CAST(SERVERPROPERTY('MachineName') AS VARCHAR(50)),
      CAST( SERVERPROPERTY('ServerName') AS VARCHAR(50)),
      GETDATE()
    FROM FROM DELETED;

The question that I have is that three tables are involved (Speakers, Sermons, and ShootLog).
Speakers table is related to sermons and ShootLog tables by SpeakerID.

Sermons table is related to ShootLog by SermonID.

Speakers
SpeakerID PK int
other column names (will provide if requested)

Sermons
SermonID PK int
SpeakerID int FK (foreign key to Speakers table)
Other column names (will provide if requested)

ShootLog
LogID PK int
SermonID FK (foreign key to Sermons table
SpeakerID FK (foreign key to Speakers table)
Other column names (will provide if requested)

The trigger I posted above captures deleted records ONLY from Speakers table and inserts them into SpeakersHistory table.

Do I need to modify this trigger to capture deleted records from the other two tables?

OR do I need to write three triggers, one for each table?

Data from those other two tables will need to be captured and stored into their own separate tables (Sermons and ShootLog).

As always, many thanks for your assistance.

Instead of using triggers - you should look at using temporal tables. See this document: https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables?view=sql-server-ver15

You should also review this article: https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-table-considerations-and-limitations?view=sql-server-ver15

If you are not on SQL Server 2017 or higher you might have issues with using CASCADE.

Ok, thank you very much for this link Jeff.

Just so I can digest this and build on it, as I understand it so far based on the little I have read, when you create a table say Speakers table I just posted about for instance, SQL Server will create a corresponding history table so that it kind of acts a back up copy of the table I created giving me a chance at restoring any data lost in Speakers table?

Am I on track. I will spend the next several hours digging more into it.

It is more than that - every change to the table is recorded in the history table. Update a column - a new row is added to the history with the dates of the change. Insert a new row into the current table and a new row is added to the history table...and if you delete a row from the current table, that row is deleted and a row is inserted into the history table with system generated dates showing when it was no longer effective.

Ok, great.

Thank you very much sir.

However, my fundamental (original) question is still not addressed; maybe your suggested solution can handle that as well and that is in my case, I have three tables with parent-children relationships where if you delete a row of records from the parent table, because of the Update/Delete cascade added to the children tables, all the rows for all three tables are deleted.

So, the issue is will the temporal table capture all the rows deleted from the three tables and insert them into that ONE history table or will they be added to three separate tables?

A few years ago, I created what we call Recyle Bin using asp.net.

The way this worked for us that time is that if you delete a record from a table, the record is captured and inserted into history table for that table.

Then we added a button called Restore next to each row.

This way, if the user thinks s/he may have inadvertently deleted the wrong record, s/he can click the Restore button to put that deleted record back to the original table.

I would have loved to use that approach in this case again but not sure how to do it with this parent/children relationship.

Careful now... temporal tables do NOT record INSERTs. They correctly only record UPDATEs and DELETEs. That's actually a very good thing because that means that you don't instantaneously double the size of your data just by doing an INSERT.

Inserts will stay in the original table forever unless they're updated or deleted. Then and only then will they be moved to the temporal table.

Can you post the CREATE TABLE statement for those 3 tables. I might have a better solution all the way around for such a thing but want to have a look-see before I make any suggestions. Thanks.

Dang - mis-wrote that, meant to say insert creates a new row and the history is kept from that point forward.

You would not want a single history table to contain all of the rows/data from 3 separate tables. That will be much harder to manage and maintain - because you will need the columns from every table, which will duplicate the data for each child table.

Even if these tables are one-to-one relationship, a change in one of the 'child' records would require storing the data from every related table.

Separate tables for history allows for a change to one of the child tables - without having to create a new history for the other tables.

For a recycle bin type function - you don't need to un-delete data. Provide the user with an audit report - the user then selects the correct data from each table - insert a new row. The new row now becomes the current row and the data has been 'recovered'.

This also allows for auditing to show that the user deleted an entity - then 'restored' the entity at a later date.

Sure, here is the CREATE statement. I used the Script table as... So, you may find that some unwanted stuff were added but I thought it was better to just let you see it all.

//Parent table - Speakers:
CREATE TABLE [dbo].[Speakers](
	[SpeakerID] [int] IDENTITY(1,1) NOT NULL,
	[SpeakerName] [nvarchar](75) NULL,
	[ClientName] [nvarchar](75) NULL,
	[Email] [nvarchar](50) NULL,
	[dateAdded] [datetime] NULL,
	[WebsiteURL] [nvarchar](250) NULL,
	[MinistryName] [nvarchar](150) NULL,
 CONSTRAINT [PK_Speakers] PRIMARY KEY CLUSTERED 
(
	[SpeakerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

//Child - Sermons:
CREATE TABLE [dbo].[Sermons](
	[sermonID] [int] IDENTITY(1,1) NOT NULL,
	[SpeakerID] [int] NULL,
	[dateFilmed] [datetime] NULL,
	[sermonTitle] [nvarchar](50) NULL,
	[Scripture] [nvarchar](50) NULL,
	[VideoURL] [nvarchar](50) NULL,
	[SermonNotes] [nvarchar](max) NULL,
 CONSTRAINT [PK_Sermons] PRIMARY KEY CLUSTERED 
(
	[sermonID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[Sermons]  WITH CHECK ADD  CONSTRAINT [FK_Speakers_Sermons] FOREIGN KEY([SpeakerID])
REFERENCES [dbo].[Speakers] ([SpeakerID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[Sermons] CHECK CONSTRAINT [FK_Speakers_Sermons]
GO

//GrandChild - ShootLog
CREATE TABLE [dbo].[ShootLog](
	[LogID] [int] IDENTITY(1,1) NOT NULL,
	[SpeakerID] [int] NULL,
	[SermonID] [int] NULL,
	[BroadcastDate] [datetime] NULL,
	[BroadcastLength] [nvarchar](50) NULL,
	[FacebookViewers] [numeric](18, 2) NULL,
	[Translation] [nvarchar](50) NULL,
 CONSTRAINT [PK_ShootLog] PRIMARY KEY CLUSTERED 
(
	[LogID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ShootLog]  WITH CHECK ADD  CONSTRAINT [FK_ShootLog_Sermons] FOREIGN KEY([SermonID])
REFERENCES [dbo].[Sermons] ([sermonID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[ShootLog] CHECK CONSTRAINT [FK_ShootLog_Sermons]
GO

Truly appreciate the assistance from you guys.

Ah... I'm glad I asked. That's a different kind of "parent/child" relationship than I was thinking of. I was thinking of an "Adjacency List" type of hierarchy. Your tables make up a nicely structured set.

I agree with @jeffw8713... temporal tables are the thing to do for these and you need one for each table. It's not a lot of work to do so, either. A side benefit that a lot of folks don't realize it that you can actually determine the actual data that was in place at any given point in time, not to mention the recovery method that he talked about.

The only shortcoming is that you can't add extra columns to a temporal table, such as a Modified_By column with a default of, say, ORIGINAL_LOGIN(). That means that your GUI would have to update a Modified_By column in the base table instead it being automatic and mostly non-defeatable.