Migrating Triggers
This topic discusses DDL and DML triggers and memory-optimized tables.
LOGON triggers are triggers defined to fire on LOGON events. LOGON triggers do not affect memory-optimized tables.
DDL Triggers
DDL triggers are triggers defined to fire when a CREATE, ALTER, DROP, GRANT, DENY, REVOKE, or UPDATE STATISTICS statement is executed on the database or server on which it is defined.
You cannot create memory-optimized tables if the database or server has one or more DDL trigger defined on CREATE_TABLE or any event group that includes it. You cannot drop a memory-optimized table if the database or server has one or more DDL trigger defined on DROP_TABLE or any event group that includes it.
You cannot create natively compiled stored procedures if there are one or more DDL triggers on CREATE_PROCEDURE, DROP_PROCEDURE, or any event group that includes those events.
DML Triggers
DML triggers cannot be defined on memory-optimized tables. However, In-Memory OLTP allows you to achieve an effect similar to DML triggers if you explicitly use stored procedures to insert, update, or delete data. If your system contains ad-hoc queries, convert them to use these stored procedures instead to simulate the effect of DML triggers.
Depending on the trigger event (FOR/AFTER or INSTEAD OF), you may include the content of the trigger in the appropriate stored procedure that performs INSERT, UPDATE, or DELETE on that table. For example, when migrating an AFTER INSERT trigger, you could alter the stored procedure that performs the insert operation by including the content of the trigger after the appropriate INSERT statement.
You can use an interpreted stored procedure or a natively compiled stored procedure. Most Transact-SQL constructs in an interpreted stored procedure can execute on a memory-optimized table. However, only a subset of Transact-SQL constructs are supported in natively compiled stored procedures. For information on Transact-SQL support on memory-optimized tables, see Accessing Memory-Optimized Tables Using Interpreted Transact-SQL. For information on Transact-SQL support in natively compiled stored procedures, see Transact-SQL Constructs Not Supported by In-Memory OLTP.
The following is a simple example of simulating DML trigger behavior on a memory-optimized table.
The database contains the following objects, scripted as CREATE TABLE, CREATE TRIGGER, and CREATE PROCEDURE statements:
CREATE TABLE OrderDetails
(
OrderId int not null primary key,
ProductId int not null,
SalePrice money not null,
Quantity int not null,
Total money not null,
IsDeleted bit not null DEFAULT (0)
)
GO
CREATE TRIGGER tr_order_details_insteadof_insert
ON OrderDetails
INSTEAD OF INSERT AS
BEGIN
DECLARE @pid int, @qty_buy int, @qty_remain int
SELECT @pid = ProductId, @qty_buy = Quantity FROM inserted
SELECT @qty_remain = Quantity FROM Inventory WHERE ProductId = @pid
IF (@qty_remain <= @qty_buy)
THROW 51000, N'Insufficient inventory!', 1
ELSE
BEGIN
INSERT INTO dbo.OrderDetails (OrderId, ProductId, SalePrice, Quantity, Total)
SELECT OrderId, ProductId, SalePrice, Quantity, Total FROM inserted
UPDATE Inventory SET Quantity = Quantity - @qty_buy WHERE ProductId = @pid
END
END
GO
CREATE TRIGGER tr_order_details_after_update
ON OrderDetails
AFTER UPDATE AS
BEGIN
INSERT INTO UpdateNotifications (OrderId, UpdateTime) SELECT OrderId, GETDATE() FROM inserted
END
GO
CREATE PROCEDURE sp_insert_order_details
@OrderId int, @ProductId int, @SalePrice money, @Quantity int, @total money
AS BEGIN
INSERT INTO dbo.OrderDetails (OrderId, ProductId, SalePrice, Quantity, Total)
VALUES (@OrderId, @ProductId, @SalePrice, @Quantity, @total)
END
GO
CREATE PROCEDURE sp_update_order_details_by_id
@OrderId int, @ProductId int, @SalePrice money, @Quantity int, @Total money
AS BEGIN
UPDATE dbo.OrderDetails
SET ProductId = @ProductId, SalePrice = @SalePrice, Quantity = @Quantity, Total = @total
WHERE OrderId = @OrderId
END
GO
The following objects are functionally equivalent to the pre-migration state:
CREATE TABLE OrderDetails
(
OrderId int not null PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1048576),
ProductId int not null,
SalePrice money not null,
Quantity int not null,
Total money not null,
IsDeleted bit not null DEFAULT (0)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO
CREATE TABLE Inventory
(
ProductId int not null PRIMARY KEY NONCLUSTERED,
Quantity int not null
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO
CREATE TABLE UpdateNotifications
(
OrderId int not null,
UpdateTime datetime2 not null
CONSTRAINT pk_updateNotifications PRIMARY KEY NONCLUSTERED (OrderId, UpdateTime)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO
CREATE PROCEDURE sp_insert_order_details
@OrderId int, @ProductId int, @SalePrice money, @Quantity int, @total money
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'English')
DECLARE @qty_remain int
SELECT @qty_remain = Quantity FROM dbo.Inventory WHERE ProductId = @ProductId
IF (@qty_remain <= @Quantity)
THROW 51000, N'Insufficient inventory!', 1
ELSE
BEGIN
INSERT INTO dbo.OrderDetails (OrderId, ProductId, SalePrice, Quantity, Total)
VALUES (@OrderId, @ProductId, @SalePrice, @Quantity, @total)
UPDATE dbo.Inventory SET Quantity = Quantity - @Quantity WHERE ProductId = @ProductId
END
END
GO
CREATE PROCEDURE sp_update_order_details_by_id
@OrderId int, @ProductId int, @SalePrice money, @Quantity int, @Total money
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'English')
UPDATE dbo.OrderDetails
SET ProductId = @ProductId, SalePrice = @SalePrice, Quantity = @Quantity, Total = @total
WHERE OrderId = @OrderId
INSERT INTO dbo.UpdateNotifications (OrderId, UpdateTime) VALUES (@OrderId, GETDATE())
END
GO