SQL server--triggers

A trigger is a special kind of stored procedure that executes automatically when a table update, insert, or DELETE statement is executed.

Example 1.

-- Example 1. Using a trigger to display ' change a piece of data '  Use  Gocreatetrigger on infoforinsert,  Deleteasprint' changes a data 'go

Example 2.

Example 3.

--Example 3: When the fruit table changes numbers column, add data to the OrderDetails table fruit number, number change. Go Create TriggerTr_fruit_buy2 onFruit for Update,Insert,Delete as--data at the forefront of operationsDeclare @num1 intSelect @num1=Numbers fromdeleted--the data after the OperationDeclare @num2 intSelect @num2=Numbers frominserted--Poor DataDeclare @num intSet @num=@num1-@num2--Remove the fruit number from the deleted tableDeclare @code varchar( -)Select @code=Ids fromdeleted--Enter data difference and fruit number into OrderDetails tableInsert  intoOrderDetails (Ordercode,[Count])Values(@code,@num)GoSelect *  fromFruitSelect *  fromOrderDetails--Add a trigger, execute the following statement, trigger two triggers tr_fruitbuy and TR_FRUIT_BUY2 results are as followsUpdateFruitSetNumbers=Numbers-5 whereIds='k002'Select *  fromOrderDetails

Example 4

--example: Instead OF trigger, delete data of info table, first delete work and family data, and then delete info corresponding dataSelect *  fromInfoSelect *  from  Work Select *  fromFamilyGoCreate TriggerTr_person_deletet onInfoinstead of Delete as--Delete work-related data      --Remove the required delete informationDeclare @code varchar( -)Select @code=Code fromdeleted--DeleteDelete  from  Work whereInfocode=@code --Delete family related dataDelete  fromFamilywhereInfocode=@code --finally delete the related data in the Info table (the info table is the main table of the family table and the work table, before the deletion can be performed)Delete  fromInfowhereCode=@codeGo--To perform a delete after adding a triggerDelete  fromInfowhereCode='P002'

SQL server--triggers