WEX5 tutorial on graphic design of intelligent database (1) Trigger

One: Design requirements:

Intelligent database design, in fact, there is no such argument, just because I design video multicast family needs, the design of the database has some intelligent requirements.

There are three purposes:

1. Reduce the amount of database code in the foreground backend operations

2. Database operation failure can be rolled back. Ensure that the database is complete and correct.

3. Take advantage of database performance.

The trigger was used today.

Example is one of my previous design requirements, in the drama management backstage after the popular drama's ID added to the popular table, drama Courisian field auto-fill hot mark for 1. The previous design is to operate the table in the foreground, and today the function monitors the action table with the trigger to automatically populate the fields. The simple idea is to use a trigger to monitor changes to a table to modify another table.

Two: Effect demonstration:

1. Copy needs to be set to popular drama ID

2. Paste the ID into the Hot Management ID table to determine

3. Hot Table ID Update

4.chinese Table Hot field auto-fill;

Three. Code logic and implementation:

Database write operation, for WEX5 is relatively simple, here only to say the use of triggers and considerations

1. Create a trigger:

In the Heidisql database management tool provided by WEX5, right-click.

2. Writing SQL statements

Description: 1. Name, which is the trigger name. In the table, which table is the monitor table.

2 event, trigger type according to the event there are three categories, namely, add, delete, modify: Insert,delete,updata.

According to the order of execution there are before: before, after: after.

After the combination, there are 6 types of triggers.

3.update Chinese set chinese.carousel=true where Id=new.chineseid;

Chnese for the table to be modified, set Chinese.corousel is the field when the table to be modified, where Id=new.chineseid is the condition. New.chineseid the field value that was written for the monitoring table. Old.chineseid the value before the monitoring table is modified. This is the trigger feature, which greatly reduces the judgment and data operation of the front and back end.

Note: 1. The trigger monitoring table cannot monitor self-written self. That is, you can only modify other tables.

The delimiter delimiter is used in 2.mysql and is not required in Heidisql. Written but could not be carried out.

WEX5 tutorial on graphic design of intelligent database (1) Trigger