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

This article gives you an example to share the specific code for MySQL to create a trigger for your reference. The details are as follows.

Let's start with an example:

# create TABLE t_attendance (job_no VARCHAR (30) DEFAULT'', operateTime VARCHAR (20) DEFAULT'', INDEX index_operateTime (operateTime), INDEX index_jobNo (job_no)) ENGINE=INNODB DEFAULT CHARSET=utf8# build trigger DELIMITER $CREATE TRIGGER t_afterinsert_on_doorlog AFTER INSERT ON t_doorlog FOR EACH ROW BEGIN DECLARE jobno VARCHAR (30); DECLARE time1 VARCHAR (20); SET jobno = (SELECT job_num FROM tb_park_user_base WHERE card_num = new.cardNo) SET time1 = DATE_FORMAT (new.operateTime,'%Y%m%d%H%i%s'); INSERT INTO t_attendance (job_no, operateTime) VALUES (jobno,time1); END$

Instance resolution:

First create the table t_attendance to be stored in the trigger

DELIMITER $means to tell mysql that the following execution statement ends with "$". Without this sentence, the following statement will go wrong and mysql will not be able to determine which sentence ends.

CREATE TRIGGER t_afterinsert_on_doorlog AFTER INSERT ON t_doorlog FOR EACH ROW syntax for creating triggers where AFTER sibling keywords are BEFORE, INSERT sibling keywords and DELETE,UPDATE

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

SET jobno=.... means to assign a value to a local variable, and the right side of = can be a query statement or function method.

The role of supplementary triggers:

Trigger is a special stored procedure that triggers execution when inserting, deleting or modifying data in a particular table. It has finer and more complex data control capabilities than the standard functions of the database itself.

Database triggers have the following functions:

1. Security. You can give the user some right to operate the database based on the value of the database.

# users' actions can be limited based on time, for example, database data is not allowed to be modified after work and holidays.

# users' operations can be restricted based on the data in the database, for example, stock prices are not allowed to rise by more than 10% at a time.

two。 Audit. You can track the user's actions on the database.

# Audit statements for users to operate the database.

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

3. Implement complex data integrity rules

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

# provide variable defaults.

4. Implement complex non-standard database-related integrity rules. Triggers can make serial updates to the relevant tables in the database. For example, a delete trigger on the author_code column of the auths table can cause matching rows in other tables to be deleted accordingly.

# cascade modifies or deletes matching rows in other tables when modifying or deleting.

# set the matching rows in other tables to null values when modifying or deleting.

# concatenate matching rows in other tables to default values when modifying or deleting.

# triggers can reject or roll back changes that undermine relevant integrity and cancel transactions that attempt to update data. This trigger works when a foreign key that does not match its primary key is inserted. For example, you can generate an insert trigger on a 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. Copy the data in the table synchronously and in real time.

6. Automatically calculate the data value, if the value of the data meets certain requirements, then carry out specific processing. For example, if the amount of money in the company's account is less than 50,000 yuan, send warning data to the financial staff immediately.

The above is the whole content of this article, I hope it will be helpful to your study, and I also hope that you will support it.

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.