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.
The main difference between the two is when the triggers are fired. The AFTER trigger will fire after the INSERT, UPDATE, or DELETE statement has 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 considered to be part of the same transaction as the DML statement that caused the trigger to fire.
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.
To see how a trigger is created we must first create a test environment. In the following code, We will create two tables named Orders and Inventory. We will then run an INSERT statement to put some sample data into the Inventory table.
We have created our tables for our test environment. We will now create an AFTER TRIGGER that will be attached to the dbo.Orders table. This trigger will check the quantity of the order being inserted and compare it against the quantity of the dbo.Inventory table.
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.
(Key note: This trigger was previously created with the CREATE TRIGGER statement noted at the top of this post. This next code sample is using the ALTER TRIGGER statement to modify the original 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.
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 Inventory table. You will see that we will get the ‘Low Inventory’ message and also that the transaction was ended within the trigger.
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.
Finally, if we no longer wanted the trigger attached to the table we would simply need to run a DROP TRIGGER statement.
For additional information on code used in this demonstration.