Shulou(Shulou.com)06/01 Report--

The function of trigger

Monitor changes in data to achieve associated operations when data changes, but it doesn't feel useful. The function of triggers will be more formal if you use code logic to control the feel. After all, the rest of the team will not necessarily see the triggers you define, but they will definitely see your code. Then why learn triggers? some knowledge is not for yourself. If you don't need it, you can't guarantee that others won't use it. If others use it and we don't know anything about it, it will bring great difficulties to the maintenance of the program.

Definition of trigger

Format: create trigger trigger name event on table name execution code

Event: insert insert, delete delete, modify update timing: there are six cases of after event and timing combination before before and after before insertafter insertbefore deleteafter deletebefore updateafter update creation syntax

Delimeter $/ / modifies the statement Terminator to avoid conflicts with statements in the definition trigger

Create trigger triggerName

After/before insert/update/delete on table name

For each row / / this line is fixed

Begin

Sql statement / / one or more sql statements, one sql statement can also omit begin and end

End

Delimeter; / / change back to the normal Terminator

New / old

If you need to use the value in the row that triggered the trigger within the trigger, then new represents the new line and old represents the old line. That is, old / new represent the row record before / after the event, respectively. It's a whole line, not a field.

* Note: insert operation has no old,delete operation, no new operation.

Administrative deletion of triggers

Drop trigger trigger_name

View all triggers

Show triggers

Example

Suppose we have a class table.

Idclass_namenum100112002130030

And a student form.

Idnameclass_id1xiaohong12xiaogang2

We define a trigger so that every time one student is added, the number of students in the corresponding class table num increases by one.

Delimiter $$create trigger zhaosheng after insert on student for each rowbeginupdate class set num=num+1 where id=new.class_id;end;$$delimiter

Depending on the effect:

When a student zhangsan is added, the num with an id of 3 in the calss table automatically increases by 1.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.