There are two types of DML Triggers that can be written for each INSERT, UPDATE, or DELETE statement. Either AFTER triggers or INSTEAD OF triggers. (Documentation)

The AFTER Trigger

The main difference between the two is when the triggers are fired. The AFTER trigger will fire after the DML statements have been executed and the data in the database has been modified. The trigger will only fire once even if multiple rows are being modified and is part of the same transaction as the DML statement that caused the trigger to fire.

The INSTEAD OF Trigger

An INSTEAD OF trigger will execute code other than the original INSERT, UPDATE, or DELETE. This is especially helpful when modifying data on a view that has been built from multiple tables.

Triggers in Action

To see how a trigger is created we must first create a test environment. The following code will create two tables named Orders and Inventory. We will then run an INSERT statement to put some sample data into the Inventory table.

CREATE TABLE dbo.Orders
(OrderID tinyint IDENTITY,
 ProdID tinyint,
 Quantity tinyint)
GO

CREATE TABLE dbo.Inventory
(InventoryID tinyint IDENTITY,
 ProdID tinyint,
 Quantity tinyint)
GO

INSERT INTO dbo.Inventory
VALUES (100,5), (101,5), (102,10)
GO

Now an AFTER TRIGGER will be created on the dbo.Orders table. This trigger will check the quantity of the order being inserted. It will the compare it against the quantity of the dbo.Inventory table.

Creating the Trigger

If the quantity being ordered is greater than the amount in the Inventory table, then the transactions will be rolled back. This includes the original INSERT transaction that cause the trigger to fire. If there is enough inventory to place the order than the amount of the order will be subtracted from the Inventory table and the order will be complete.

CREATE TRIGGER Order_Inventory_Insert
ON dbo.Orders
AFTER INSERT AS
BEGIN
--Turn off count rows returned
SET NOCOUNT ON 

--Declare three variables to hold values
DECLARE @OrderQty tinyint, 
		@InvQty tinyint, 
		@ProdID tinyint

--Get order information from Inventory and inserted table
SELECT @OrderQty = i.Quantity,
	   @InvQty = Inv.Quantity,
	   @ProdID = i.ProdID
FROM inserted as i
	JOIN dbo.Inventory as Inv
	ON i.ProdID = Inv.ProdID

--Compare Order Quantity vs Inventory Quantity
IF @OrderQty > @InvQty
	BEGIN
		PRINT 'Low Inventory'
		ROLLBACK TRANSACTION
	END
ELSE
	BEGIN
		UPDATE dbo.Inventory
		SET Quantity = 
			@InvQty - @OrderQty
		WHERE ProdID = @ProdID
		PRINT 'Order Complete'
	END
END

Testing the Trigger

Our trigger is now in place to let us test it out. First, we will run a SELECT statement against the dbo.Inventory table to verify the values are in that table.

SELECT * FROM dbo.Inventory

Now let us run an INSERT statement on the dbo.Orders table for the ProductID of 100 that has a quantity greater than the amount in the dbo.Inventory table. You will see that we will get the ‘Low Inventory’ message and also that the transaction was ended within the trigger.

INSERT dbo.Orders VALUES (100,7)

If we ran the same INSERT statement but with the quantity being less than the amount in the Inventory table, we will get a different result.

INSERT dbo.Orders VALUES (100,2)

Finally, if we no longer wanted the trigger attached to the table we would simply need to run a DROP TRIGGER statement.

DROP TRIGGER Order_Inventory_Insert

Documentation

For additional information on code used in this demonstration.

• How to use the CREATE TABLE statement click here
• How to work with IDENTITY values click here
• How to work with DML statements click here.
• How to work with JOIN types click here.
• Working with the inserted/deleted virtual tables click here.
• Learn more about the SET NOCOUNT ON statement click here.