MySQL How to Create Triggers

In this article, we share the specific code of MySQL to create triggers for your reference. The specific contents are as follows

Let's start with an example:

# Table building 
DROP TABLE IF EXISTS t_attendance;
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 parsing:

First create the table t_attendance to be stored in the trigger

DELIMITER $tells mysql that the following execution statement ends with "$". Without this sentence, the following statement will go wrong, and mysql cannot judge 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 are 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, = to the right can be a query statement or a function method

The role of supplementary triggers:

Triggers are special stored procedures that trigger execution when data in a specific table is inserted, deleted, or modified. They have 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 users some right to operate 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 the data in the database, such as not allowing the stock price to rise by more than 10% at a time.

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

# Audit statements that users manipulate the database.

# Write user updates to the database to the audit table.

3. Implement complex data integrity rules

Implement 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, triggers can reverse any futures that attempt to eat more than their own margin.

Provides variable defaults.

4. Implement complex non-standard database-related integrity rules. Triggers can make serial updates to related 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 rows in other tables that match them when modifying or deleting.

# Set matching rows in other tables to NULL values when modifying or deleting.

Set the matching row cascade in other tables to the default value when modifying or deleting.

Triggers are able to reject or roll back changes that undermine the integrity of the associated data, cancelling transactions that attempt to update the data. This trigger works when a foreign key is inserted that does not match its primary key. For example, you can generate an insert trigger on the column books.author_code, and if the new value does not match a value in the column auths.author_code, the insert is rolled back.

5. Copy the data in the table synchronously and in real time.

6. Calculate the data value automatically, and if the data value meets the requirement of 1, carry out specific processing. For example, if the funds in the company's account are less than 50,000 yuan, send warning data to the financial personnel immediately.