MySQL中触发器,使用instead of的语法,给出一个示例
时间: 2024-05-19 10:15:33 浏览: 19
Sure, here's an example:
Suppose we have two tables, "orders" and "customers", and we want to create a trigger that updates the "last_order_date" field in the "customers" table whenever a new order is inserted. We can use the "instead of" syntax to intercept the insert statement and perform the necessary updates:
CREATE TRIGGER update_customer_last_order_date
INSTEAD OF INSERT ON orders
FOR EACH ROW
BEGIN
-- update the customers table with the new last_order_date
UPDATE customers
SET last_order_date = NEW.order_date
WHERE customers.customer_id = NEW.customer_id;
-- insert the new order as usual
INSERT INTO orders (customer_id, order_date, order_total)
VALUES (NEW.customer_id, NEW.order_date, NEW.order_total);
END;
This trigger will update the "last_order_date" field in the "customers" table whenever a new order is inserted, and then insert the new order into the "orders" table.
相关推荐
![docx](https://img-home.csdnimg.cn/images/20210720083331.png)
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![-](https://csdnimg.cn/download_wenku/file_type_column_c1.png)
![-](https://csdnimg.cn/download_wenku/file_type_column_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)