Triggers for SQL Server

A trigger is a one-day T-SQL statement that SQL Server automatically executes in response to any of the following statements. Delete,insert,update. Tables and views support triggers. (but temporary tables are not supported.)

What business scenarios do we need to use triggers?

1. Each time a product is ordered, the quantity of the order is subtracted from the inventory quantity.

2. Whenever you add a customer to a database table, check that the phone number is in the correct format and that the state abbreviation is uppercase.

1. First, create the trigger. Triggers are created with the CREATE TRIGGER statement. The following is a simple example:

Create Trigger  on  Insertasselect'product added'

The trigger is defined as after insert, so this trigger executes after the INSERT statement executes successfully. Triggers are defined on a per-table basis, so that each table supports up to 3 triggers (one insert,update,delete per trigger)

2. Delete the trigger. We delete a trigger with the DROP TRIGGER statement. As shown below:

Drop Trigger Newproduct_trigger;

3. To enable and disable triggers, we use the Disable trigger and enable trigger statements:

-- Disabling triggers Trigger  on Products ; -- re-enable triggers Trigger  on products;

4. How do we determine if the table has a trigger? We can use the built-in stored procedure sp_helptrigger:

-- Sp_helptrigger takes a table name and returns a list of triggers. Sp_helptrigger Products;

5. Use the trigger.
5.1 Insert Trigger

The INSERT trigger executes after the INSERT statement executes. Within the Insert trigger code, you can refer to a virtual table named inserted to access the inserted row.

--creates an insert trigger that, when a new order is inserted, generates a new order number to be saved to Order_numCreate TriggerNeworder_trigger onOrdersafterInsert asSelect @ @identity  asOrder_num;--to test this trigger, try inserting a new line. Insert  intoOrders (ORDER_DATE,CUST_ID)Values(GetDate(),10001)--Output Order_num--20010

5.2 Delete Trigger
The DELETE trigger executes after the DELETE statement executes. Within the Delete trigger code, you can refer to a virtual table named deleted to access the deleted row.

-- use the Delete trigger to save the row that will be deleted into an archive table Create Trigger  on  Deleteasbegin    insert into orders_archive (order_num , order_date,cust_id)  Select from deleted; end;

This trigger knows when a row is deleted from the table orders. It uses the Insert SELECT statement to save the rows in the deleted to an archive table named Orders_achive.

5.3 Update Trigger

The update trigger executes after the UPDATE statement executes. In the update trigger code, you can reference a virtual table named deleted to access the previous value, referencing a virtual table named inserted to access the value of the new update.

--The following example guarantees that state abbreviations are always capitalized. Create TriggerVendor_trigger onVendorsafterInsert,Update asbegin    UpdateVendorsSetVend_state=Upper(vend_state)wherevend_idinch(Selectvend_id frominserted);end;

This trigger is executed after insert,update. Each time a row is inserted or updated, the values in Vend_state are replaced with upper (vend_state).

Triggers for SQL Server