关于喜忧参半的SQL Server触发器详解

CREATE TABLE Sales.Orders_log
( Orders_log_ID int NOT NULL IDENTITY(1,1) 
 CONSTRAINT PK_Sales_Orders_log PRIMARY KEY CLUSTERED,
 OrderID int NOT NULL,
 CustomerID_Old int NOT NULL,
 CustomerID_New int NOT NULL,
 SalespersonPersonID_Old int NOT NULL,
 SalespersonPersonID_New int NOT NULL,
 PickedByPersonID_Old int NULL,
 PickedByPersonID_New int NULL,
 ContactPersonID_Old int NOT NULL,
 ContactPersonID_New int NOT NULL,
 BackorderOrderID_Old int NULL,
 BackorderOrderID_New int NULL,
 OrderDate_Old date NOT NULL,
 OrderDate_New date NOT NULL,
 ExpectedDeliveryDate_Old date NOT NULL,
 ExpectedDeliveryDate_New date NOT NULL,
 CustomerPurchaseOrderNumber_Old nvarchar(20) NULL,
 CustomerPurchaseOrderNumber_New nvarchar(20) NULL,
 IsUndersupplyBackordered_Old bit NOT NULL,
 IsUndersupplyBackordered_New bit NOT NULL,
 Comments_Old nvarchar(max) NULL,
 Comments_New nvarchar(max) NULL,
 DeliveryInstructions_Old nvarchar(max) NULL,
 DeliveryInstructions_New nvarchar(max) NULL,
 InternalComments_Old nvarchar(max) NULL,
 InternalComments_New nvarchar(max) NULL,
 PickingCompletedWhen_Old datetime2(7) NULL,
 PickingCompletedWhen_New datetime2(7) NULL,
 LastEditedBy_Old int NOT NULL,
 LastEditedBy_New int NOT NULL,
 LastEditedWhen_Old datetime2(7) NOT NULL,
 LastEditedWhen_New datetime2(7) NOT NULL,
 ActionType VARCHAR(6) NOT NULL,
 ActionTime DATETIME2(3) NOT NULL,
UserName VARCHAR(128) NULL);

CREATE TRIGGER TR_Sales_Orders_Audit
 ON Sales.Orders
 AFTER INSERT, UPDATE, DELETE
AS
BEGIN
 SET NOCOUNT ON;
 INSERT INTO Sales.Orders_log
 (OrderID, CustomerID_Old, CustomerID_New, 
 SalespersonPersonID_Old, SalespersonPersonID_New, 
 PickedByPersonID_Old, PickedByPersonID_New,
 ContactPersonID_Old, ContactPersonID_New, 
 BackorderOrderID_Old, BackorderOrderID_New, 
 OrderDate_Old, OrderDate_New, ExpectedDeliveryDate_Old,
 ExpectedDeliveryDate_New, 
 CustomerPurchaseOrderNumber_Old, 
 CustomerPurchaseOrderNumber_New, 
 IsUndersupplyBackordered_Old, 
 IsUndersupplyBackordered_New,
 Comments_Old, Comments_New, 
 DeliveryInstructions_Old, DeliveryInstructions_New, 
 InternalComments_Old, InternalComments_New, 
 PickingCompletedWhen_Old,
 PickingCompletedWhen_New, LastEditedBy_Old, 
 LastEditedBy_New, LastEditedWhen_Old, 
 LastEditedWhen_New, ActionType, ActionTime, UserName)
 SELECT
 ISNULL(Inserted.OrderID, Deleted.OrderID) AS OrderID,
 Deleted.CustomerID AS CustomerID_Old,
 Inserted.CustomerID AS CustomerID_New,
 Deleted.SalespersonPersonID AS SalespersonPersonID_Old,
 Inserted.SalespersonPersonID AS SalespersonPersonID_New,
 Deleted.PickedByPersonID AS PickedByPersonID_Old,
 Inserted.PickedByPersonID AS PickedByPersonID_New,
 Deleted.ContactPersonID AS ContactPersonID_Old,
 Inserted.ContactPersonID AS ContactPersonID_New,
 Deleted.BackorderOrderID AS BackorderOrderID_Old,
 Inserted.BackorderOrderID AS BackorderOrderID_New,
 Deleted.OrderDate AS OrderDate_Old,
 Inserted.OrderDate AS OrderDate_New,
 Deleted.ExpectedDeliveryDate 
 AS ExpectedDeliveryDate_Old,
 Inserted.ExpectedDeliveryDate 
 AS ExpectedDeliveryDate_New,
 Deleted.CustomerPurchaseOrderNumber 
 AS CustomerPurchaseOrderNumber_Old,
 Inserted.CustomerPurchaseOrderNumber 
 AS CustomerPurchaseOrderNumber_New,
 Deleted.IsUndersupplyBackordered 
 AS IsUndersupplyBackordered_Old,
 Inserted.IsUndersupplyBackordered 
 AS IsUndersupplyBackordered_New,
 Deleted.Comments AS Comments_Old,
 Inserted.Comments AS Comments_New,
 Deleted.DeliveryInstructions 
 AS DeliveryInstructions_Old,
 Inserted.DeliveryInstructions 
 AS DeliveryInstructions_New,
 Deleted.InternalComments AS InternalComments_Old,
 Inserted.InternalComments AS InternalComments_New,
 Deleted.PickingCompletedWhen 
 AS PickingCompletedWhen_Old,
 Inserted.PickingCompletedWhen 
 AS PickingCompletedWhen_New,
 Deleted.LastEditedBy AS LastEditedBy_Old,
 Inserted.LastEditedBy AS LastEditedBy_New,
 Deleted.LastEditedWhen AS LastEditedWhen_Old,
 Inserted.LastEditedWhen AS LastEditedWhen_New,
 CASE
 WHEN Inserted.OrderID IS NULL THEN 'DELETE'
 WHEN Deleted.OrderID IS NULL THEN 'INSERT'
 ELSE 'UPDATE'
 END AS ActionType,
 SYSUTCDATETIME() ActionTime,
 SUSER_SNAME() AS UserName
 FROM Inserted
 FULL JOIN Deleted
 ON Inserted.OrderID = Deleted.OrderID;
END

UPDATE Orders
 SET InternalComments = 'Item is no longer backordered',
 BackorderOrderID = NULL,
 IsUndersupplyBackordered = 0,
 LastEditedBy = 1,
 LastEditedWhen = SYSUTCDATETIME()
FROM sales.Orders
WHERE Orders.OrderID = 10;

CREATE TRIGGER TR_Sales_Orders_Process
 ON Sales.Orders
 AFTER INSERT
AS
BEGIN
 SET NOCOUNT ON;
 DECLARE @count INT;
 SELECT @count = COUNT(*) FROM inserted;
 DECLARE @min_id INT;
 SELECT @min_id = MIN(OrderID) FROM inserted;
 DECLARE @current_id INT = @min_id;
 WHILE @current_id < @current_id + @count
 BEGIN
 EXEC dbo.process_order_fulfillment 
 @OrderID = @current_id;
 SELECT @current_id = @current_id + 1;
 END
END

CREATE TYPE dbo.udt_OrderID_List AS TABLE(
 OrderID INT NOT NULL,
 PRIMARY KEY CLUSTERED 
( OrderID ASC));
GO
CREATE TRIGGER TR_Sales_Orders_Process
 ON Sales.Orders
 AFTER INSERT
AS
BEGIN
 SET NOCOUNT ON;
 DECLARE @OrderID_List dbo.udt_OrderID_List;
 EXEC dbo.process_order_fulfillment @OrderIDs = @OrderID_List;
END

/* 12/29/2020 EHP
 This trigger logs all changes to the table to the Orders_log
 table that occur for non-internal customers.
 CustomerID = -1 signifies an internal/test customer and 
 these are not audited.
*/
CREATE TRIGGER TR_Sales_Orders_Audit
 ON Sales.Orders
 FOR INSERT, UPDATE, DELETE
AS
BEGIN
 SET NOCOUNT ON;
 INSERT INTO Sales.Orders_log
 (OrderID, CustomerID_Old, CustomerID_New, 
 SalespersonPersonID_Old, SalespersonPersonID_New,
 PickedByPersonID_Old, PickedByPersonID_New,
 ContactPersonID_Old, ContactPersonID_New, 
 BackorderOrderID_Old, BackorderOrderID_New, 
 OrderDate_Old, OrderDate_New, 
 ExpectedDeliveryDate_Old,
 ExpectedDeliveryDate_New, 
 CustomerPurchaseOrderNumber_Old, 
 CustomerPurchaseOrderNumber_New, 
 IsUndersupplyBackordered_Old, 
 IsUndersupplyBackordered_New,
 Comments_Old, Comments_New, 
 DeliveryInstructions_Old, DeliveryInstructions_New, 
 nternalComments_Old, InternalComments_New, 
 PickingCompletedWhen_Old, PickingCompletedWhen_New, 
 LastEditedBy_Old, LastEditedBy_New, 
 LastEditedWhen_Old, LastEditedWhen_New, 
 ActionType, ActionTime, UserName)
 SELECT
 ISNULL(Inserted.OrderID, Deleted.OrderID) AS OrderID, 
 -- The OrderID can never change. 
 --This ensures we get the ID correctly, 
 --regardless of operation type.
 Deleted.CustomerID AS CustomerID_Old,
 Inserted.CustomerID AS CustomerID_New,
 Deleted.SalespersonPersonID AS SalespersonPersonID_Old,
 Inserted.SalespersonPersonID AS SalespersonPersonID_New,
 Deleted.PickedByPersonID AS PickedByPersonID_Old,
 Inserted.PickedByPersonID AS PickedByPersonID_New,
 Deleted.ContactPersonID AS ContactPersonID_Old,
 Inserted.ContactPersonID AS ContactPersonID_New,
 Deleted.BackorderOrderID AS BackorderOrderID_Old,
 Inserted.BackorderOrderID AS BackorderOrderID_New,
 Deleted.OrderDate AS OrderDate_Old,
 Inserted.OrderDate AS OrderDate_New,
 Deleted.ExpectedDeliveryDate AS ExpectedDeliveryDate_Old,
 Inserted.ExpectedDeliveryDate AS ExpectedDeliveryDate_New,
 Deleted.CustomerPurchaseOrderNumber 
 AS CustomerPurchaseOrderNumber_Old,
 Inserted.CustomerPurchaseOrderNumber 
 AS CustomerPurchaseOrderNumber_New,
 Deleted.IsUndersupplyBackordered 
 AS IsUndersupplyBackordered_Old,
 Inserted.IsUndersupplyBackordered 
 AS IsUndersupplyBackordered_New,
 Deleted.Comments AS Comments_Old,
 Inserted.Comments AS Comments_New,
 Deleted.DeliveryInstructions 
 AS DeliveryInstructions_Old,
 Inserted.DeliveryInstructions 
 AS DeliveryInstructions_New,
 Deleted.InternalComments AS InternalComments_Old,
 Inserted.InternalComments AS InternalComments_New,
 Deleted.PickingCompletedWhen AS PickingCompletedWhen_Old,
 Inserted.PickingCompletedWhen 
 AS PickingCompletedWhen_New,
 Deleted.LastEditedBy AS LastEditedBy_Old,
 Inserted.LastEditedBy AS LastEditedBy_New,
 Deleted.LastEditedWhen AS LastEditedWhen_Old,
 Inserted.LastEditedWhen AS LastEditedWhen_New,
 CASE -- Determine the operation type based on whether 
 --Inserted exists, Deleted exists, or both exist.
 WHEN Inserted.OrderID IS NULL THEN 'DELETE'
 WHEN Deleted.OrderID IS NULL THEN 'INSERT'
 ELSE 'UPDATE'
 END AS ActionType,
 SYSUTCDATETIME() ActionTime,
 SUSER_SNAME() AS UserName
 FROM Inserted
 FULL JOIN Deleted
 ON Inserted.OrderID = Deleted.OrderID
 WHERE Inserted.CustomerID <> -1 
 -- -1 indicates an internal/non-production 
 --customer that should not be audited.
 OR Deleted.CustomerID <> -1; 
 -- -1 indicates an internal/non-production 
 --customer that should not be audited.
END

CREATE TRIGGER TR_Sales_Orders_Log_BackorderID_Change
 ON Sales.Orders
 AFTER UPDATE
AS
BEGIN
 SET NOCOUNT ON;
 IF UPDATE(BackorderOrderID)
 BEGIN
 UPDATE OrderBackorderLog
 SET BackorderOrderID = Inserted.BackorderOrderID,
 PreviousBackorderOrderID = Deleted.BackorderOrderID
 FROM dbo.OrderBackorderLog
 INNER JOIN Inserted
 ON Inserted.OrderID = OrderBackorderLog.OrderID
 END
END

CREATE TRIGGER TR_Sales_Orders_I
 ON Sales.Orders
 AFTER INSERT
CREATE TRIGGER TR_Sales_Orders_IU
 ON Sales.Orders
 AFTER INSERT, UPDATE
CREATE TRIGGER TR_Sales_Orders_UD
 ON Sales.Orders
 AFTER UPDATE, DELETE
CREATE TRIGGER TR_Sales_Orders_UID
 ON Sales.Orders
 AFTER UPDATE, INSERT, DELETE
CREATE TRIGGER TR_Sales_Orders_ID
 ON Sales.Orders
 AFTER INSERT, DELETE

ALTER DATABASE WideWorldImporters 
SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON;
ALTER DATABASE WideWorldImporters ADD FILEGROUP WWI_InMemory_Data 
 CONTAINS MEMORY_OPTIMIZED_DATA;
ALTER DATABASE WideWorldImporters ADD FILE 
 (NAME='WideWorldImporters_IMOLTP_File_1', 
 FILENAME='C:/SQLData/WideWorldImporters_IMOLTP_File_1.mem') 
 TO FILEGROUP WWI_InMemory_Data;

CREATE TYPE dbo.SalesOrderMetadata
AS TABLE
( OrderID INT NOT NULL PRIMARY KEY NONCLUSTERED,
 CustomerID INT NOT NULL,
 SalespersonPersonID INT NOT NULL,
 ContactPersonID INT NOT NULL,
 INDEX IX_SalesOrderMetadata_CustomerID NONCLUSTERED HASH 
 (CustomerID) WITH (BUCKET_COUNT = 1000))
WITH (MEMORY_OPTIMIZED = ON);

CREATE TABLE dbo.OrderAdjustmentLog
( OrderAdjustmentLog_ID int NOT NULL IDENTITY(1,1) 
 CONSTRAINT PK_OrderAdjustmentLog PRIMARY KEY CLUSTERED,
 OrderID INT NOT NULL,
 CustomerID INT NOT NULL,
 SalespersonPersonID INT NOT NULL,
 ContactPersonID INT NOT NULL,
CreateTimeUTC DATETIME2(3) NOT NULL);

CREATE TRIGGER TR_Sales_Orders_Mem_Test
 ON Sales.Orders
 AFTER UPDATE
AS
BEGIN
 SET NOCOUNT ON;
 DECLARE @OrderData dbo.SalesOrderMetadata;
 INSERT INTO @OrderData
 (OrderID, CustomerID, SalespersonPersonID, 
 ContactPersonID)
 SELECT
 OrderID,
 CustomerID,
 SalespersonPersonID,
 ContactPersonID
 FROM Inserted;
 
 DELETE OrderData
 FROM @OrderData OrderData
 INNER JOIN sales.Customers
 ON Customers.CustomerID = OrderData.CustomerID
 WHERE Customers.IsOnCreditHold = 0;
 UPDATE OrderData
 SET ContactPersonID = 1
 FROM @OrderData OrderData
 WHERE OrderData.ContactPersonID IS NULL;
 
 INSERT INTO dbo.OrderAdjustmentLog
 (OrderID, CustomerID, SalespersonPersonID, 
 ContactPersonID, CreateTimeUTC)
 SELECT
 OrderData.OrderID,
 OrderData.CustomerID,
 OrderData.SalespersonPersonID,
 OrderData.ContactPersonID,
 SYSUTCDATETIME()
 FROM @OrderData OrderData;
END

UPDATE Customers
 SET IsOnCreditHold = 1
FROM Sales.Customers
WHERE Customers.CustomerID = 832;
UPDATE Orders
 SET SalespersonPersonID = 2
FROM sales.Orders
WHERE CustomerID = 832;

ALTER TABLE Sales.Invoices WITH CHECK ADD CONSTRAINT
CK_Sales_Invoices_ReturnedDeliveryData_Must_Be_Valid_JSON
CHECK ([ReturnedDeliveryData] IS NULL OR 
ISJSON([ReturnedDeliveryData])<>(0))

ALTER TABLE Warehouse.Colors ADD CONSTRAINT 
UQ_Warehouse_Colors_ColorName UNIQUE NONCLUSTERED (ColorName ASC);

ALTER TABLE Sales.Orders WITH CHECK ADD CONSTRAINT
FK_Sales_Orders_CustomerID_Sales_Customers FOREIGN KEY (CustomerID)
REFERENCES Sales.Customers (CustomerID);

CREATE FUNCTION Website.CalculateCustomerPrice
 (@CustomerID INT, @StockItemID INT, @PricingDate DATE)
RETURNS DECIMAL(18,2)
WITH EXECUTE AS OWNER
AS
BEGIN
 DECLARE @CalculatedPrice decimal(18,2);
 DECLARE @UnitPrice decimal(18,2);
 DECLARE @LowestUnitPrice decimal(18,2);
 DECLARE @HighestDiscountAmount decimal(18,2);
 DECLARE @HighestDiscountPercentage decimal(18,3);
 DECLARE @BuyingGroupID int;
 DECLARE @CustomerCategoryID int;
 DECLARE @DiscountedUnitPrice decimal(18,2);
 SELECT @BuyingGroupID = BuyingGroupID,
 @CustomerCategoryID = CustomerCategoryID
 FROM Sales.Customers
 WHERE CustomerID = @CustomerID;
 SELECT @UnitPrice = si.UnitPrice
 FROM Warehouse.StockItems AS si
 WHERE si.StockItemID = @StockItemID;
 SET @CalculatedPrice = @UnitPrice;
 SET @LowestUnitPrice = (
 SELECT MIN(sd.UnitPrice)
 FROM Sales.SpecialDeals AS sd
 WHERE ((sd.StockItemID = @StockItemID) 
 OR (sd.StockItemID IS NULL))
 AND ((sd.CustomerID = @CustomerID) 
 OR (sd.CustomerID IS NULL))
 AND ((sd.BuyingGroupID = @BuyingGroupID) 
 OR (sd.BuyingGroupID IS NULL))
 AND ((sd.CustomerCategoryID = @CustomerCategoryID) 
 OR (sd.CustomerCategoryID IS NULL))
 AND ((sd.StockGroupID IS NULL) OR EXISTS (SELECT 1 
 FROM Warehouse.StockItemStockGroups AS sisg
 WHERE sisg.StockItemID = @StockItemID
 AND sisg.StockGroupID = sd.StockGroupID))
 AND sd.UnitPrice IS NOT NULL
 AND @PricingDate BETWEEN sd.StartDate AND sd.EndDate);
 IF @LowestUnitPrice IS NOT NULL AND @LowestUnitPrice < @UnitPrice
 BEGIN
 SET @CalculatedPrice = @LowestUnitPrice;
 END;
 SET @HighestDiscountAmount = (
 SELECT MAX(sd.DiscountAmount)
 FROM Sales.SpecialDeals AS sd
 WHERE ((sd.StockItemID = @StockItemID) 
 OR (sd.StockItemID IS NULL))
 AND ((sd.CustomerID = @CustomerID) 
 OR (sd.CustomerID IS NULL))
 AND ((sd.BuyingGroupID = @BuyingGroupID) 
 OR (sd.BuyingGroupID IS NULL))
 AND ((sd.CustomerCategoryID = @CustomerCategoryID) 
 OR (sd.CustomerCategoryID IS NULL))
 AND ((sd.StockGroupID IS NULL) OR EXISTS 
 (SELECT 1 FROM Warehouse.StockItemStockGroups AS sisg 
 WHERE sisg.StockItemID = @StockItemID
 AND sisg.StockGroupID = sd.StockGroupID))
 AND sd.DiscountAmount IS NOT NULL
 AND @PricingDate BETWEEN sd.StartDate AND sd.EndDate);
 IF @HighestDiscountAmount IS NOT NULL AND (
 @UnitPrice - @HighestDiscountAmount) < @CalculatedPrice
 BEGIN
 SET @CalculatedPrice = @UnitPrice - @HighestDiscountAmount;
 END;
 SET @HighestDiscountPercentage = (
 SELECT MAX(sd.DiscountPercentage)
 FROM Sales.SpecialDeals AS sd
 WHERE ((sd.StockItemID = @StockItemID)
 OR (sd.StockItemID IS NULL))
 AND ((sd.CustomerID = @CustomerID) 
 OR (sd.CustomerID IS NULL))
 AND ((sd.BuyingGroupID = @BuyingGroupID) 
 OR (sd.BuyingGroupID IS NULL))
 AND ((sd.CustomerCategoryID = @CustomerCategoryID) 
 OR (sd.CustomerCategoryID IS NULL))
 AND ((sd.StockGroupID IS NULL) OR EXISTS 
 (SELECT 1 FROM Warehouse.StockItemStockGroups AS sisg
 WHERE sisg.StockItemID = @StockItemID
 AND sisg.StockGroupID = sd.StockGroupID))
 AND sd.DiscountPercentage IS NOT NULL
 AND @PricingDate BETWEEN sd.StartDate AND sd.EndDate);
 IF @HighestDiscountPercentage IS NOT NULL
 BEGIN
 SET @DiscountedUnitPrice = ROUND(@UnitPrice * 
 @HighestDiscountPercentage / 100.0, 2);
 IF @DiscountedUnitPrice < @CalculatedPrice 
 SET @CalculatedPrice = @DiscountedUnitPrice;
 END;
 RETURN @CalculatedPrice;
END;