跨Lab15DB_1、Lab15DB_2数据库的Author与Book表级联更新SQL触发器创建请求

Alright, let's walk through setting up those cascading update triggers for your Author (parent) and Book (child) tables in both Lab15DB_1 and Lab15DB_2. First, I noticed your Book table create statement was cut off—since it's a child table of Author, it must have an AuthorId foreign key column linking to the parent table. I'll include that in the complete schema first to make sure we're working with the full picture.

Step 1: Verify/Complete the Book Table Schema #

First, let's make sure your Book table includes the foreign key to Author. Here's the full create statement with the missing AuthorId column and foreign key constraint (adjust if your existing schema already has this):

-- For Lab15DB_1
USE Lab15DB_1;
GO

CREATE TABLE Book (
    BookId INT NOT NULL PRIMARY KEY,
    BookName NVARCHAR(50) NOT NULL,
    BookType NVARCHAR(50) NOT NULL CHECK(BookType IN ('Science', 'Hobby', 'Cooking', 'Fishing', 'Nature', 'Favorites')),
    PublishYear INT NOT NULL,
    AuthorId INT NOT NULL, -- Foreign key linking to Author table
    FOREIGN KEY (AuthorId) REFERENCES Author(AuthorId)
);
GO

-- Repeat the same for Lab15DB_2
USE Lab15DB_2;
GO

CREATE TABLE Book (
    BookId INT NOT NULL PRIMARY KEY,
    BookName NVARCHAR(50) NOT NULL,
    BookType NVARCHAR(50) NOT NULL CHECK(BookType IN ('Science', 'Hobby', 'Cooking', 'Fishing', 'Nature', 'Favorites')),
    PublishYear INT NOT NULL,
    AuthorId INT NOT NULL,
    FOREIGN KEY (AuthorId) REFERENCES Author(AuthorId)
);
GO

Step 2: Create Cascading Update Triggers #

The goal here is to create a trigger on the Author table that automatically updates the AuthorId in the Book table whenever an author's AuthorId is modified. We'll create this trigger separately for each database, and make sure it handles multi-row updates correctly.

Trigger for Lab15DB_1 #

USE Lab15DB_1;
GO

CREATE TRIGGER trg_Author_Update_Cascade
ON Author
AFTER UPDATE
AS
BEGIN
    -- Prevent extra result sets and set safe transaction isolation
    SET NOCOUNT ON;
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

    -- Update Book entries to match the new AuthorId, even for multiple updated authors
    UPDATE b
    SET b.AuthorId = i.AuthorId
    FROM Book b
    INNER JOIN DELETED d ON b.AuthorId = d.AuthorId
    INNER JOIN INSERTED i ON d.AuthorName = i.AuthorName AND d.AuthorSurname = i.AuthorSurname;
END
GO

Trigger for Lab15DB_2 #

Just repeat the same trigger logic for the second database:

USE Lab15DB_2;
GO

CREATE TRIGGER trg_Author_Update_Cascade
ON Author
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

    UPDATE b
    SET b.AuthorId = i.AuthorId
    FROM Book b
    INNER JOIN DELETED d ON b.AuthorId = d.AuthorId
    INNER JOIN INSERTED i ON d.AuthorName = i.AuthorName AND d.AuthorSurname = i.AuthorSurname;
END
GO

Key Notes to Keep in Mind #

• Test with Sample Data: Always test the trigger first—update an author's AuthorId and check if the corresponding Book entries reflect the new ID.
• Unique Name/Surname: The trigger uses AuthorName and AuthorSurname to pair old and updated author rows. If these aren't unique in your Author table, add another unchanging unique identifier (like a separate AuthorUUID column) to ensure correct mapping.
• Built-in Alternative: If you don't need custom logic beyond cascading updates, you could modify the foreign key constraint to use ON UPDATE CASCADE directly (no trigger needed):

  FOREIGN KEY (AuthorId) REFERENCES Author(AuthorId) ON UPDATE CASCADE;
  

内容的提问来源于stack exchange，提问作者Sleppy