Export (0) Print
Expand All

Migrating Triggers

SQL Server 2014

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 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 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
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft