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.