MySQL trigger-conditional trigger syntax

article for the author original, without permission, no reprint. -sun Yat-sen University Feng XingweiExperiment4Trigger

( 1 ) Experimental purposes

Mastering the design and use of database triggers

( 2 ) experimental content and requirements

definition before triggers and After Trigger. Be able to understand the action and execution principle of different types of triggers and verify the validity of triggers.

( 3 ) experimental emphases and difficulties

Experimental focus: Definition of triggers.

Experimental difficulty: Using triggers to achieve more complex user-defined integrity.

The experiment also used the university database which appeared in the theoretical textbook as an experimental example.

(1). after trigger

I. An update trigger is defined on the takes table, and when the attribute grade of the tuple in the takes table is updated, it means that the student's class score is updated, and the tot_cred attribute of the student tuple needs to be maintained with a trigger to keep the total credits in real time updated. Triggers are fired only if the attribute grade is updated from null or ' F ' to represent a specific score that the course has completed.

delimiterCreate TriggerCredit_get afterUpdate  onTakes forEach rowbegin    if(New.grade<> 'F'  andNew.grade is  not NULL                and(Old.grade= 'F' orOld.grade is NULL))        Then    UpdateStudentSetTot_cred=Tot_cred+              (Selectcredits fromCoursewherecourse.course_id=new.course_id)whereStudent.id=new.id;End if; End;

II. An INSERT trigger is defined on the section table, and the trigger is started after any insert operation on the section table to ensure that the time_slot_id attribute of the insert tuple is legal for the Class period, and is subject to the time_slot_ of the Time_slot table ID constraint to check referential integrity when inserting.

delimiterCreate TriggerTimeslot_check1 afterInsert onSection forEach rowbegin       if(new.time_slot_id not inch               (Selecttime_slot_id fromTime_slot))  Then       Delete  from Sectionwheretime_slot_id=new.time_slot_id;End if;End

III. A DELETE trigger is defined on the Time_slot table, and when the deleted tuple's time_slot_id is not in the deleted Time_slot table, and the tuple containing the time_slot_id value exists in the section, the trigger is started. To ensure that the referential integrity of the section occurs when a delete is occurring in the time_slot.

delimiterCreate TriggerTimeslot_check2 afterDelete onTime_slot forEach rowbegin       if(old.time_slot_id not inch              (Selecttime_slot_id fromTime_slot) andold.time_slot_idinch               (Selecttime_slot_id fromSection ))  Then       Insert  intoTime_slotValues(old);End if;End

Iv. verifying the After UPDATE trigger defined on the takes table

Initial number 999 students in 493 of this course score is F, that is, fail to obtain credit:

And it has been learned to be divided into 15:

If the result is B after the retake, you need to update takes:

Defines the UPDATE trigger trigger on the takes table, at which point the student's total credits should be increased:

You can see that the update trigger triggered successfully.

(2). before trigger

The Update,insert,delete statement syntax of the before trigger is basically similar to the after trigger, so don't repeat all three statements below, just take the before insert trigger as an example.

I. Define a before insert trigger on the takes table, when inserting a record, assuming that the fraction of the inserted

A blank value indicates that the score is missing, so defining this trigger triggers a null value instead of white space when the score value condition is met.

delimiterCreate TriggerSetNull beforeInsert  onTakes forEach rowbegin       if(New.grade= ' ')     Then     SetNew.grade= NULL;End if;End

ii Validating before insert triggers defined on a takes table

Initial time:

Students numbered 999 have only one course.

Suppose the student is now in another class, inserting a tuple, and the lack of the course's results has not yet been given:

To view information about the student's course:

You can see from the table above that the before insert trigger on the takes table is successful.

iii Delete Trigger

Drop trigger SetNull;

Summarize:

After trigger-is triggered after recording manipulation, is the first to complete the data additions and deletions, and then trigger, trigger the statement later than the monitoring of the deletion and modification operation, can not affect the previous additions and deletions to change the action
Before trigger-is triggered before recording manipulation, is the first to complete the trigger, then increase the deletion, trigger the statement before the deletion of the monitoring changes, we have the opportunity to judge, modify the impending operation, such as: we need to determine the new value and the old value of the size or relationship before triggering, if the requirements are triggered, Do not pass the modification and trigger, such as: table between the definition of a foreign key, in the deletion of the primary key, you must first delete the foreign key table, then there are successively points, here before equivalent to set the breakpoint, we can handle the deletion of foreign keys.

For INSERT statements, only new is valid;

For DELETE statements, only old is valid;

For UPDATE statements, NEW and old can be used at the same time.

MySQL trigger-conditional trigger syntax