INSTEAD OF 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 instead of the DML event
The DML event does not run anymore
Used with INSERT, UPDATE, and DELETE statements for tables or views

INSTEAD OF trigger prerequisites

| ID  | Customer        | Product   | Price | Quantity | OrderDate  | TotalAmount |
|-----|-----------------|-----------|-------|----------|------------|-------------|
| 284 | VitaFruit       | Orange    | 0.76  | 75       | 2018-09-17 | 57.00       |
| 330 | Healthy Choices | Raspberry | 1.96  | 200      | 2018-10-29 | 392.00      |
| 302 | World of Fruits | Pineapple | 4.16  | 25       | 2018-10-09 | 104.00      |
| 248 | Fruit Mania     | Plum      | 1.11  | 300      | 2018-08-11 | 333.00      |
| 358 | Fruit Mag       | Pomelo    | 1.11  | 750      | 2018-11-29 | 832.50      |
| 100 | VitaFruit       | Avocado   | 2.91  | 500      | 2018-03-29 | 1455.00     |

INSTEAD OF trigger prerequisites

Target table
Description of the trigger
Trigger firing event (DML)
Trigger name

Orders
Prevent updates of existing entries
UPDATE
PreventOrdersUpdate

INSTEAD OF trigger definition

CREATE TRIGGER PreventOrdersUpdate

ON Orders

INSTEAD OF UPDATE

AS
    RAISERROR ('Updates on "Orders" table are not permitted.
                Place a new order to add new products', 16, 1);

Let's practice!

Building and Optimizing Triggers in SQL Server