How to create a trigger in MySQL

This article example shares the specific code for creating a MySQL trigger for your reference. The specific content is as follows

Let's take an example:

#Create table 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


#Create 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$

Example analysis:

First create the table t_attendance to be stored in the trigger

DELIMITER $ means telling MySQL that the following statement ends with "$". Without this sentence, the following statement will fail and MySQL cannot determine which statement ends.

CREATE TRIGGER t_afterinsert_on_doorlog AFTER INSERT ON t_doorlog FOR EACH ROW Syntax for creating a trigger: AFTER has BEFORE as a keyword at the same level, and INSERT has DELETE and UPDATE as keywords at the same level.

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. The right side of = can be a query statement or a function method.

Supplementary triggers:

A trigger is a special stored procedure that is executed when data in a specific table is inserted, deleted, or modified. It has more sophisticated and complex data control capabilities than the standard functions of the database itself.

Database triggers have the following functions:

1. Security. Based on the values ​​of the database, users can be given certain rights to operate the database.

# You can restrict user operations based on time, for example, you cannot modify database data after get off work or on holidays.

# You can restrict user operations based on the data in the database, for example, not allowing the stock price to increase by more than 10% at a time.

2. Audit. Can track user operations on the database.

# Audit statements used by 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, a trigger could back out any attempt to take on futures that exceed one's margin.

# Provides mutable default values.

4. Implement complex, non-standard database-related integrity rules. Triggers can perform serial updates on related tables in the database. For example, a delete trigger on the author_code column of the auths table can cause corresponding deletes of matching rows in other tables.

# When modifying or deleting, cascade modify or delete matching rows in other tables.

# When modifying or deleting, set the matching rows in other tables to NULL values.

# When modifying or deleting, set the matching rows in other tables to the default value in cascade.

# Triggers can reject or roll back changes that destroy relevant integrity and cancel transactions attempting to update data. This trigger is triggered when a foreign key is inserted that does not match its primary key. For example, you could create an insert trigger on the books.author_code column so that if the new value doesn't match a value in the auths.author_code column, the insert is rolled back.

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

6. Automatically calculate data values, and if the data value reaches certain requirements, perform specific processing. For example, if the funds in the company's account are less than 50,000 yuan, a warning data will be sent immediately to the financial personnel.

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.