SQL statement (21)--Trigger (DML trigger)

TriggerI. Overview of triggers (special stored procedures)

Definition:

The stored procedure that is executed when the data for the specified table value is modified . The difference is that the execution of the stored procedure is invoked using the EXEC statement , and the execution of the trigger does not need to be invoked using the EXEC statement .

function:

1. Implementing referential integrity and data consistency for replication that cannot be guaranteed by primary key and foreign key

2. He was able to cascade changes to related tables in the database

3. Provides more complex data integrity than check constraints, and customizes error messages.

Category:

1. Data manipulation language triggers DML

   • Database operation language: Update,delete

2. Data definition language Trigger DDL

   • Logging Database Change events

Ii. Creating a DML trigger

• INSERT Trigger
• DELETE Trigger
• UPDATE Trigger
• Alternative triggers
• Allow nested triggers to be used
• Recursive triggers

5.25: Create a trigger named Employee_deleted on the employee table, its function: When the table is deleted, first check whether the employee is a ' personnel department ' staff, if not can be deleted, otherwise undelete and display ' cannot delete '

Create TriggerEmployee_delete onDepartmentafterDelete as    Begindeclary@x Char(Ten)    Select @x =Departmentname--variables from the database, with the from     fromDepartmentif(@x = 'Personnel')--No, just use the variable .    Begin            Print 'Cannot delete'        RollBack             --revocation is rollback .    EndEnd--ExecutionDelete  fromDepartmentWhereSname= 'Personnel'

• Delete First, then undo

Create TriggerEmployee_delete onEmployeeafterDelete asBegin    Declare @Dp varchar( -)    Select @Dp =Departmentname fromDepartment D1, Deleted D2WhereD1. DepartmentID=D2. DepartmentIDIf(@Dp = 'Personnel')    Begin        Print 'Cannot delete'        RollBack    EndEnd

• Update triggers (returns updated records)

Create Triggeremployee_update onStu_infoafterUpdate asBegin    Declare @StuCount Int    Select @StuCount = Count(*)     fromStu_infoUpdateStu_sumSet  Number = @StuCount    Selects_id asPre-update student number, S_name asname of student before update fromDeletedSelects_id asPost-update student number, S_name asname of student after update fromInsertedEnd--ExecutionUpdateStu_infoSetS_name= 'Zhang San'Wheres_id= 1

• Instead of (substitution trigger) first judgment, then operation

Create TriggerEmployee_delete onEmployeeinstead of Delete asBegin    Declare @Dp varchar( -)    Select @Dp =Departmentname fromDepartmentIf(@Dp = 'Personnel')    Begin        Print 'Cannot delete'    EndEnd

• Prohibit inserting record operations directly into a table

  create   Trigger   Insert_forbidden  on  Span style= "color: #000000;" > Stu_sumafter  insert  as  begin  raiserror  ( "  It is not allowed to insert records directly into this table, the operation is forbidden  , 1 , 1  )  rollback  transaction  end    

• Trigger nesting

  • Server-> properties, Miscellaneous--Allow triggers to fire other triggers--true

• Recursive triggers

  • Recursive trigger enabled, miscellaneous, properties------------

SQL statement (21)--Trigger (DML trigger)