Trigger:

Trigger usage scenarios and corresponding versions:

This is the 26th day of my participation in the August Text Challenge.More challenges in August

Triggers can be used with MySQL versions:

  • Version: MySQL5 and above

Examples of usage scenarios:

  1. Each time you add a customer to a database table, check that the phone number is in the correct format and that the state abbreviation is capitalized
  2. Each time 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 an archive table

That is, automatic processing when a table changes.

If the trigger error message “Not allowed to return a result set from a trigger” is displayed; Please go to the end to see the 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
Copy the code

Results:

INSERT INTO products VALUES('demo2'.'1003'.'xiaoguo'.'66.6'.'hello world')
> 1644- Products Added > Time:0.035s
Copy the code

Explanation:

Start by creating a trigger:

# newProduct The name of the trigger
CREATE TRIGGER newproduct
Copy the code

Trigger time:

BEFORE: Triggers fire BEFORE firing their statements

AFTER: Triggers fire AFTER the statement that triggered them is complete

Here we use after; That is, it fires the condition after the insert is done;

DECLARE msg VARCHAR(100);
Copy the code

Note: The DECLARE statement is an instruction that declares 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;
Copy the code

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

“HY000 “is called a” common error “:

If the command gets a generic error, the message in the following message is fired;

Note: this statement is only personal understanding, but also a little understanding, if there is a better explanation, welcome to leave a message.

Trigger conditions start with BEGIN and END with END.

Triggering event:

  1. insert
  2. update
  3. delete

Delete trigger:

Drop trigger
DROP TRIGGER newproduct;
Copy the code

INSERT trigger:

Insert triggers execute before or after an INSERT statement. Note the following:

  1. Within the INSERT trigger code. You can reference a virtual table named NEW to access the inserted row;
  2. In the before INSERT trigger, values in NEW can also be updated (allowing changes to inserted values)
  3. For AUTO_INCREMENT columns, NEW contains 0 before the insert and a NEW auto-generated value after the insert

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;
Copy the code

Explanation:

Create a trigger for neworder that executes after the insert and on each insert row, with a virtual table identical to the Orders table represented by NEW in the insert.

SELECT NEW.order_num into @a;
Copy the code

Find the number of the data we inserted in the virtual table and store it in variable A;

Detection:

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

Insert data, output the number of the inserted data

Delete:

drop TRIGGER neworder;
Copy the code

Delete the trigger.

Example 2:

Create a trigger on the COURSE table and check if a record with the same COURSE name appears when you insert it.

CREATE TRIGGER trg_course_in BEFORE INSERT ON courseFOR EACH ROWBEGIN        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 
Copy the code

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

CREATE TRIGGER trg_ssex AFTER INSERT on student FOR EACH ROWBEGIN DECLARE msg VARCHAR(100); IF(NEW. Ssex not in(' male ',' female ') THENSET msg ='Sex must be male or female';  	SIGNAL SQLSTATE 'HY000' SET message_text = msg; END IFEND
Copy the code

UPDATE trigger:

  1. In the code of the update trigger, you can reference a virtual table named OLD to access the previous value, i.e. the value before the update was executed, or a virtual table named NEW to access the newly updated value.
  2. In the before UPDATE trigger, the value in NEW may also be updated (allowing you to change the value that will be used in the UPDATE statement);
  3. Values in OLD are read-only and cannot be updated.

Example 1: Make sure the state initials are uppercase

CREATE TRIGGER UPDATEevendor BEFORE UPDATE on vendorsFOR EACH ROW SET new.vend_state =UPPER(new.vend_state);UPDATE vendors SET vend_state='hw' where vend_id='1001';DROP TRIGGER UPDATEevendor;
Copy the code

Note: Upper: converts text to uppercase:

Example 2: SNO in student is not allowed to be modified. If this column is modified, an error message is displayed and the operation is cancelled.

CREATE TRIGGER trg_student_updateSno BEFORE UPDATEFOR EACH ROWBEGIN DECLARE msg VARCHAR(100);   IF NEW.sno <> OLD.sno THEN  	SET msg='Sno modification is not allowed';     SIGNAL SQLSTATE 'HY000' SET message_text = msg;   END IF; END
Copy the code

DELETE trigger:

The DELETE trigger executes before or after the DELETE statement executes:

  1. Within the delete trigger code, you can reference the OLD virtual table 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 into an archive table

Start by creating a table similar to Orders:

CREATE TABLE archive_orders LIKE orders;
Copy the code
CREATE TRIGGER deleteOrder BEFORE DELETE on ordersfor EACH ROW BEGININSERT INTO archive_orders(order_num,order_date,cust_id) VALUES(old.order_num,old.order_date,old.cust_id); END
Copy the code

Explanation:

When deleting information from rows in the ORDER table, save the deleted information to archive_Orders;

Drop a row from table 1;

DELETE FROM orders WHERE order_num='20014';
Copy the code

View the effect:

 SELECT * FROM archive_orders;
Copy the code

Question:

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

  1. Cause: Triggers returning result sets are not supported after MySQL5
  2. Workaround: Add in@ variable name after the following statement
  3. Take data: select@variable name

Explained in detail: www.programmersought.com/article/323…

Create a user variables: blog.csdn.net/JesseYoung/…

The end:

If you see this or happen to help you, please click 👍 or ⭐ thank you;

There are mistakes, welcome to point out in the comments, the author will see the modification.