问SQL创建触发器

EN

-- **********************
-- Very simple datamodel:
-- **********************
CREATE TABLE Customer(
    IdCustomer int not null, -- PK
    IsLoyalCustomer bit not null default 0)
GO

CREATE TABLE Rental(
    IdRental int not null, -- PK for Rental
    IdCustomer int not null) -- FK to Customer
GO

-- ********************
-- Here is the trigger:
-- ********************
CREATE Trigger RentalTrigger ON Rental
AFTER INSERT, UPDATE, DELETE
AS
BEGIN

-- Set based operation, handling insert, update and delete commands:
-- Find all customers involved in this set based operation (=> RentalCustomers)
-- Inner Join With Rental => Gives all rentals where these RentalCustomers are involved
-- Recalculate IsLoyalCustomer for each RentalCustomer
-- Update those Customer in the Customer table where IsLoyalCustomer is changed.
UPDATE Customer SET IsLoyalCustomer = RentalCustomerStats.IsLoyalCustomer
FROM
(SELECT 
    Rental.IdCustomer, 
    IsLoyalCustomer = CASE WHEN COUNT(*) < 10 THEN 0 ELSE 1 END
FROM
    Rental
    INNER JOIN (
        SELECT DISTINCT IdCustomer FROM inserted
        UNION
        SELECT DISTINCT IdCustomer FROM deleted) AS RentalCustomers 
            ON Rental.IdCustomer = RentalCustomers.IdCustomer
    GROUP BY Rental.IdCustomer)
AS RentalCustomerStats
WHERE
    Customer.IdCustomer = RentalCustomerStats.IdCustomer
    AND Customer.IsLoyalCustomer <> RentalCustomerStats.IsLoyalCustomer;
END;
GO

-- ********
-- Testing:
-- ********
-- Add Customer:
INSERT INTO Customer(IdCustomer) VALUES(1);
-- Add Purchases for this customer:
INSERT INTO Rental(IdCustomer, IdRental) VALUES(1,1);
INSERT INTO Rental(IdCustomer, IdRental) VALUES(1,2);
INSERT INTO Rental(IdCustomer, IdRental) VALUES(1,3);
INSERT INTO Rental(IdCustomer, IdRental) VALUES(1,4);
INSERT INTO Rental(IdCustomer, IdRental) VALUES(1,5);
INSERT INTO Rental(IdCustomer, IdRental) VALUES(1,6);
INSERT INTO Rental(IdCustomer, IdRental) VALUES(1,7);
INSERT INTO Rental(IdCustomer, IdRental) VALUES(1,8);
INSERT INTO Rental(IdCustomer, IdRental) VALUES(1,9);
-- Not yet loyal:
SELECT * FROM Customer;
-- Add more rentals for this customer:
INSERT INTO Rental(IdCustomer, IdRental) VALUES(1,10);
-- Now the customer is loyal:
SELECT * FROM Customer;

-- Add more rentals for this customer:
INSERT INTO Rental(IdCustomer, IdRental) VALUES(1,11);
INSERT INTO Rental(IdCustomer, IdRental) VALUES(1,12);
-- Still loyal:
SELECT * FROM Customer;

-- Remove some rentals:
DELETE FROM Rental WHERE IdRental > 9;
-- Not loyal customer anymore:
SELECT * FROM Customer;

-- The remaining rentals:
SELECT * FROM Rental;