MySQL CREATE trigger

Let's start with an example:

#建表DROP TABLE IF EXISTSt_attendance;CREATE TABLEt_attendance (Job_noVARCHAR( -)DEFAULT "', OperatetimeVARCHAR( -)DEFAULT "',  INDEXIndex_operatetime (operatetime),INDEXindex_jobno (Job_no)) ENGINE=INNODBDEFAULTCHARSET=utf8# Build trigger delimiter $CREATE TRIGGERT_afterinsert_on_doorlog afterINSERT  onT_doorlog forEach ROWBEGIN  DECLAREJobnoVARCHAR( -); DECLARETime1VARCHAR( -); SETJobno=(SELECTJob_num fromTb_park_user_baseWHERECard_num=New.cardno); SETTime1=Date_format (New.operatetime,'%y%m%d%h%i%s'); INSERT  intot_attendance (Job_no, Operatetime)VALUES(jobno,time1);END$

Instance parsing:

Create the table to be stored in the trigger first t_attendance

DELIMITER $ means to tell MySQL to end the execution of the statement with "$", and if there is no such sentence, the following statement will go wrong, and MySQL cannot judge which sentence ends.

Create TRIGGER T_afterinsert_on_doorlog after the INSERT on T_doorlog to each ROW creates the syntax of the trigger where the after sibling keyword has before, in SERT Sibling keyword and delete,update

DECLARE jobno VARCHAR (30) means to declare a local variable in a trigger SQL

SET jobno= .... Assign a value to a local variable, = Right can be a query statement or a function method

The role of supplemental triggers:

A trigger is a special kind of stored procedure that triggers execution when inserting, deleting, or modifying data in a particular table, which has finer and more complex data control than the standard functionality of the database itself.

Database triggers have the following effects:

1. Security. You can make a user have some right to manipulate the database based on the value of the database.

# You can limit user actions based on time, such as not allowing database data to be modified after work and holidays.

# You can limit the user's actions based on data in the database, such as not allowing the price of the stock to increase by more than 10% at a time.

2. Audit. You can track user actions on a database.

# Audit the statements of the user operations database.

# writes the user's updates to the database to the audit table.

3. Implementing complex data integrity rules

# implements non-standard data integrity checks and constraints. Triggers can produce more complex restrictions than rules. Unlike rules, triggers can reference columns or database objects. For example, a trigger can rewind any futures that attempt to eat more than their own margin.

# provides a variable default value.

4. Implement complex non-standard database-related integrity rules. Triggers can be used for serial updates of related tables in the database. For example, a delete trigger on the Auths table author_code column causes the corresponding row to be deleted in the other table.

# cascade Modify or delete rows in other tables that match them when modified or deleted.

# Sets the row in the other table to a null value when modified or deleted.

# Sets the row cascade in the other table to be the default value when modified or deleted.

# triggers can reject or rollback changes that disrupt related integrity and cancel transactions that attempt to update data. This trigger works when you insert a foreign key that does not match its primary health. For example, an INSERT trigger can be generated on the Books.author_code column, and if the new value does not match a value in the Auths.author_code column, the insert is rolled back.

5. Synchronously replicates the data in the table in real time.

6. Automatically calculates the data value, if the value of the data meets certain requirements, the specific processing. For example, if the company's account has less than $50,000 in money, it immediately sends warning data to the financial officer.

MySQL CREATE trigger