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

 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, DeliveryIns
                
提示：
                    本文由神整理自网络，如有侵权请联系本站删除！
                    

                    本站声明： 

                    1、本站所有资源均来源于互联网，不保证100%完整、不提供任何技术支持； 

                    2、本站所发布的文章以及附件仅限用于学习和研究目的;不得将用于商业或者非法用途；否则由此产生的法律后果，本站概不负责！
                
                

                

                                            
上一篇：由于系统错误 126 (SQL Server)，指定驱动程序无法加载_MsSql_
                                                                
下一篇：SQL Server模糊查询的常见方法总结_MsSql_