AFTER triggers (DML)

Building and Optimizing Triggers in SQL Server

Florin Angelescu

Instructor

Definition and properties

  • Performs a set of actions when fired
  • The actions are performed only after the DML event is finished
  • Used with INSERT, UPDATE, and DELETE statements for tables or views
Building and Optimizing Triggers in SQL Server

AFTER trigger prerequisites

  • Table or view needed for DML statements
  • The trigger will be attached to the same table

Output from table Products used for this example:

| Product | Price | Currency | Quantity | Measure |
|---------|-------|----------|----------|---------|
| Apple   | 2.00  | USD      | 25000    | kg      |
| Apricot | 1.55  | USD      | 2500     | kg      |
| Avocado | 3.00  | USD      | 1000     | kg      |
| Banana  | 1.80  | USD      | 35000    | kg      |
Building and Optimizing Triggers in SQL Server

AFTER trigger prerequisites

  • Target table
  • Description of the trigger
  • Trigger firing event (DML)
  • Trigger name
  • Products
  • Save info of deleted products to a history table
  • DELETE
  • TrackRetiredProducts
Building and Optimizing Triggers in SQL Server

AFTER trigger prerequisites summary

When rows are removed from the Products table...

Save the required information from those rows to the table RetiredProducts.

| Product | Price | Currency | Quantity | Measure |
|---------|-------|----------|----------|---------|
| Apple   | 2.00  | USD      | 25000    | kg      |
| Apricot | 1.55  | USD      | 2500     | kg      | X
| Avocado | 3.00  | USD      | 1000     | kg      |
| Banana  | 1.80  | USD      | 35000    | kg      |

| Product | Measure | RemovalDate |
|---------|---------|-------------|
| Apricot | kg      | 19.04.2019  |
Building and Optimizing Triggers in SQL Server

AFTER trigger definition

CREATE TRIGGER TrackRetiredProducts

ON Products

AFTER DELETE

AS
    INSERT INTO RetiredProducts (Product, Measure)
    SELECT Product, Measure
    FROM deleted;
Building and Optimizing Triggers in SQL Server

"inserted" and "deleted" tables

  • Special tables used by DML triggers
  • Created automatically by SQL Server
Building and Optimizing Triggers in SQL Server

"inserted" and "deleted" tables

  • Special tables used by DML triggers
  • Created automatically by SQL Server
Special table INSERT UPDATE DELETE
inserted new rows new rows N/A
deleted N/A updated rows removed rows
Building and Optimizing Triggers in SQL Server

The complete AFTER trigger

CREATE TRIGGER TrackRetiredProducts
ON Products
AFTER DELETE
AS
    INSERT INTO RetiredProducts (Product, Measure)
    SELECT Product, Measure
    FROM deleted;
Building and Optimizing Triggers in SQL Server

Let's practice!

Building and Optimizing Triggers in SQL Server

Preparing Video For Download...