SQL开发知识：关于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;