MySQL trigger usage scenarios and method examples

trigger:

Trigger usage scenarios and corresponding versions:

The trigger can be used with the following MySQL versions:

• Version: MySQL5 or above
  

Example usage scenarios:

1. Whenever a customer is added to a database table, check that the phone number is in the correct format and that the state abbreviation is capitalized.
2. Whenever a product is ordered, the quantity ordered is subtracted from the quantity in stock.
3. Whenever a row is deleted, a copy is kept in some archive table
   

That is: automatically processed when a table is changed.

If you encounter a trigger error "Not allowed to return a result set from a trigger", please scroll to the end for details.

Use of triggers:

Create a basic trigger:

CREATE TRIGGER newproduct AFTER INSERT on products FOR EACH ROW
BEGIN
 DECLARE msg VARCHAR(100);
 SET msg = "products added";
 SIGNAL SQLSTATE 'HY000' SET message_text = msg;
 
END

result:

INSERT INTO products VALUES('demo2','1003','xiaoguo','66.6','hello world')
> 1644 - products added
> Time: 0.035s

explain:

First create a trigger:

#newproduct The name of the trigger CREATE TRIGGER newproduct

Trigger timing:

BEFORE: Triggers fire before the statement that fired them

AFTER: Triggers fire after the statement that triggered them completes.

Here we use after; that is, the trigger condition is after the insertion is completed;

DECLARE msg VARCHAR(100);

Note: declare statement is an instruction to declare variables in a compound statement; if msg is not declared, MySQL will report an error when executing the statement;

SIGNAL SQLSTATE 'HY000' SET message_text = msg;

If the SIGNAL statement indicates a specific SQLSTATE value, that value is used to represent the specified condition.

"HY000" is called a "general error":

If a general error occurs in the command, the message in the following message will be triggered;

Note: This statement is just my personal understanding, and I only have a limited understanding of it. If you have a better explanation, please leave a message.

The trigger condition starts with BEGIN and ends with END.

Triggering event:

1. insert
2. update
3. delete
   

To delete a trigger:

-- Delete the trigger DROP TRIGGER newproduct;

INSERT trigger:

The insert trigger is executed before or after the insert statement is executed. The following points should be noted:

1. Inside the insert trigger code. You can reference a virtual table named NEW to access the inserted row;
2. In a before insert trigger, the value in NEW can also be updated (allowing the inserted value to be changed)
3. For AUTO_INCREMENT columns, NEW contains 0 before the insert is executed and contains the new automatically generated value after the insert is executed.
   

Example: When inserting a new order, generate a new order number and save it to order_num

CREATE TRIGGER neworder AFTER INSERT ON orders for EACH ROW
 SELECT NEW.order_num into @ee;

insert INTO orders(order_date,cust_id) VALUES(NOW(),10001);
SELECT @ee as num;

drop TRIGGER neworder;

explain:

Create a neworder trigger that is executed after insert and for each inserted row. In insert, there is a virtual table that is exactly the same as the orders table, represented by NEW.

SELECT NEW.order_num into @a;

Find the number of the data we inserted in the virtual table and save the number in the a variable;

Detection:

insert INTO orders(order_date,cust_id) VALUES(NOW(),10001);
SELECT @ee as num;

Insert data and output the number of the inserted data

delete:

drop TRIGGER neworder;

Delete the trigger.

Example 2:

Create a trigger on the COURSE table to check whether there are records with the same course name when inserting. If so, no action will be taken.

CREATE TRIGGER trg_course_in 
BEFORE INSERT ON course
FOR EACH ROW
BEGIN
  DECLARE msg VARCHAR(100); 
  IF EXISTS (SELECT * FROM course where cname=NEW.cname) THEN 
   SET msg = 'Cannot enter a course with the same name'; 
   SIGNAL SQLSTATE 'HY000' SET message_text = msg; 
  END IF; 
 
END

Example 3: When inserting information into the student table, check that the value of ssex must be male or female.

CREATE TRIGGER trg_ssex AFTER INSERT on student FOR EACH ROW
BEGIN
 DECLARE msg VARCHAR(100);
 IF(NEW.ssex not in('男','女')) THEN
 SET msg = 'Gender must be male or female'; 
 SIGNAL SQLSTATE 'HY000' SET message_text = msg;
 END IF
END

UPDATE trigger:

1. In the code of the update trigger, you can reference a virtual table named OLD to access the previous value, that is, the value before the update is executed, and you can also reference a virtual table named NEW to access the newly updated value;
2. In a before update trigger, the value in NEW may also be updated (allowing modification of the value to be used in the update statement);
3. All values ​​in OLD are read-only and cannot be updated.
   

Example 1: Ensure that the state abbreviation is capitalized

CREATE TRIGGER UPDATEevendor BEFORE UPDATE on vendors
FOR EACH ROW SET new.vend_state =UPPER(new.vend_state);

UPDATE vendors SET vend_state='hw' where vend_id='1001';
DROP TRIGGER UPDATEevendor;

Note: upper: converts the text to uppercase:

Example 2: It is not allowed to modify the student ID sno in the student table. If this column is modified, an error message will be displayed and the operation will be canceled.

CREATE TRIGGER trg_student_updateSno BEFORE UPDATE
FOR EACH ROW
BEGIN
 DECLARE msg VARCHAR(100); 
 IF NEW.sno <> OLD.sno THEN 
 SET msg = 'Modification of sno is not allowed'; 
 SIGNAL SQLSTATE 'HY000' SET message_text = msg; 
 END IF; 
END

DELETE trigger:

A DELETE trigger is executed before or after the delete statement is executed:

1. In the delete trigger code, you can reference the virtual table of OLD to access the deleted row;
2. All values ​​in OLD are read-only and cannot be updated
   

example:

Use old to save the rows to be deleted in an archive table

First, create a table similar to orders:

CREATE TABLE archive_orders LIKE orders;

-- Create a delete trigger CREATE TRIGGER deleteorder BEFORE DELETE on orders
for EACH ROW BEGIN
INSERT INTO archive_orders(order_num,order_date,cust_id) VALUES(old.order_num,old.order_date,old.cust_id);
END

explain:

When deleting information in a row in the order table, save the deleted information to archive_orders;

Delete a row from the original table:

DELETE FROM orders WHERE order_num='20014';

See the effect:

SELECT * FROM archive_orders;

Finish:

Note: If you encounter a trigger error "Not allowed to return a result set from a trigger"

1. Reason: Since MySQL 5, triggers are not supported to return result sets.
2. Solution: Add into @ variable name after the following statement
3. Get data: select @variable name
   

Detailed explanation: https://www.programmersought.com/article/3237975256/

Create user variables: https://www.jb51.net/article/201843.htm

This concludes this article on the usage scenarios and methods of MySQL triggers. For more information on the use of MySQL triggers, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!