基于BankingDB的多表视图创建及INSTEAD OF触发器数据同步实现

Got it, let's walk through how to implement this for your BankingDB. You've got those linked tables and need views with INSTEAD OF triggers (or stored procedures) to sync inserts to the underlying tables. Here's a step-by-step breakdown that's practical and follows best practices:

We'll start by building a view that pulls together all related data from your tables. This view will act as the single point for users to interact with the combined bank-branch-customer data:

CREATE VIEW vwBankCustomerDetails
AS
SELECT 
    b.BankID,
    b.BankName,
    br.BranchID,
    br.BranchName,
    bt.BranchTypeName,
    a.AddressID,
    a.Street,
    a.City,
    c.CustomerID,
    c.CustomerName,
    c.Email
FROM tblBank b
JOIN tblbrBranch br ON b.BankID = br.BankID
JOIN tblbtBranchType bt ON br.BranchTypeID = bt.BranchTypeID
JOIN tbladdAddress a ON br.AddressID = a.AddressID
JOIN tblcstCustomer c ON br.BranchID = c.BranchID;

This view joins all your core tables to present a unified dataset, making it easier for users to work with without needing to understand the underlying table relationships.

Since multi-table linked views can't be directly inserted into by default, we'll use an INSTEAD OF trigger to route the insert data to the correct underlying tables. The trigger will handle foreign key dependencies and avoid duplicate entries:

CREATE TRIGGER trg_vwBankCustomerDetails_Insert
ON vwBankCustomerDetails
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;

    -- Insert into tblBank only if the bank doesn't already exist
    INSERT INTO tblBank (BankName)
    SELECT DISTINCT i.BankName
    FROM inserted i
    WHERE NOT EXISTS (SELECT 1 FROM tblBank b WHERE b.BankName = i.BankName);

    -- Insert into tblbtBranchType if the branch type is new
    INSERT INTO tblbtBranchType (BranchTypeName)
    SELECT DISTINCT i.BranchTypeName
    FROM inserted i
    WHERE NOT EXISTS (SELECT 1 FROM tblbtBranchType bt WHERE bt.BranchTypeName = i.BranchTypeName);

    -- Insert new addresses (match on street + city to avoid duplicates)
    INSERT INTO tbladdAddress (Street, City)
    SELECT DISTINCT i.Street, i.City
    FROM inserted i
    WHERE NOT EXISTS (SELECT 1 FROM tbladdAddress a WHERE a.Street = i.Street AND a.City = i.City);

    -- Insert new branches, linking to existing bank, branch type, and address
    INSERT INTO tblbrBranch (BankID, BranchName, BranchTypeID, AddressID)
    SELECT 
        b.BankID,
        i.BranchName,
        bt.BranchTypeID,
        a.AddressID
    FROM inserted i
    JOIN tblBank b ON b.BankName = i.BankName
    JOIN tblbtBranchType bt ON bt.BranchTypeName = i.BranchTypeName
    JOIN tbladdAddress a ON a.Street = i.Street AND a.City = i.City
    WHERE NOT EXISTS (
        SELECT 1 FROM tblbrBranch br 
        WHERE br.BankID = b.BankID AND br.BranchName = i.BranchName
    );

    -- Finally, insert the customer linked to the new/existing branch
    INSERT INTO tblcstCustomer (CustomerName, Email, BranchID)
    SELECT 
        i.CustomerName,
        i.Email,
        br.BranchID
    FROM inserted i
    JOIN tblBank b ON b.BankName = i.BankName
    JOIN tblbrBranch br ON br.BankID = b.BankID AND br.BranchName = i.BranchName;
END;

The trigger runs instead of the default insert operation, ensuring data is added to each table in the correct order to respect foreign key constraints.

If you want more flexibility (like adding validation, error handling, or custom logic), a stored procedure is a great alternative. It uses transactions to ensure data consistency across all tables:

CREATE PROCEDURE sp_InsertBankCustomerDetails
    @BankName VARCHAR(100),
    @BranchName VARCHAR(100),
    @BranchTypeName VARCHAR(50),
    @Street VARCHAR(200),
    @City VARCHAR(100),
    @CustomerName VARCHAR(100),
    @Email VARCHAR(100)
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION;

        -- Declare variables to hold auto-generated IDs
        DECLARE @BankID INT, @BranchTypeID INT, @AddressID INT, @BranchID INT;

        -- Get existing Bank ID, or insert new bank if it doesn't exist
        SELECT @BankID = BankID FROM tblBank WHERE BankName = @BankName;
        IF @BankID IS NULL
        BEGIN
            INSERT INTO tblBank (BankName) VALUES (@BankName);
            SET @BankID = SCOPE_IDENTITY();
        END;

        -- Get existing Branch Type ID, or insert new type
        SELECT @BranchTypeID = BranchTypeID FROM tblbtBranchType WHERE BranchTypeName = @BranchTypeName;
        IF @BranchTypeID IS NULL
        BEGIN
            INSERT INTO tblbtBranchType (BranchTypeName) VALUES (@BranchTypeName);
            SET @BranchTypeID = SCOPE_IDENTITY();
        END;

        -- Get existing Address ID, or insert new address
        SELECT @AddressID = AddressID FROM tbladdAddress WHERE Street = @Street AND City = @City;
        IF @AddressID IS NULL
        BEGIN
            INSERT INTO tbladdAddress (Street, City) VALUES (@Street, @City);
            SET @AddressID = SCOPE_IDENTITY();
        END;

        -- Get existing Branch ID, or insert new branch
        SELECT @BranchID = BranchID FROM tblbrBranch WHERE BankID = @BankID AND BranchName = @BranchName;
        IF @BranchID IS NULL
        BEGIN
            INSERT INTO tblbrBranch (BankID, BranchName, BranchTypeID, AddressID)
            VALUES (@BankID, @BranchName, @BranchTypeID, @AddressID);
            SET @BranchID = SCOPE_IDENTITY();
        END;

        -- Insert the new customer
        INSERT INTO tblcstCustomer (CustomerName, Email, BranchID)
        VALUES (@CustomerName, @Email, @BranchID);

        COMMIT TRANSACTION;
        PRINT 'Data inserted successfully!';
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        PRINT 'Error occurred: ' + ERROR_MESSAGE();
        THROW;
    END CATCH;
END;

You can call this procedure like so to add new data:

EXEC sp_InsertBankCustomerDetails 'Global Bank', 'Midtown Branch', 'Commercial', '5th Ave 456', 'Chicago', 'Jane Smith', 'jane.smith@example.com';

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