这是我创建的表格 .
USE my_guitar_shop;
DROP TABLE IF EXISTS Products_Audit;
CREATE TABLE Products_Audit (
audit_id INT PRIMARY KEY,
category_id INT REFERENCES categories(category_id),
product_code VARCHAR ( 10 ) NOT NULL UNIQUE ,
product_name VARCHAR ( 255 ) NOT NULL,
list_price INT NOT NULL,
discount_percent INT NOT NULL DEFAULT 0.00 ,
date_updated DATETIME NULL);
创建名为products_after_update的触发器 . 在更新行之后,此触发器应将有关产品的旧数据插入Products_Audit表 . 然后,使用适当的UPDATE语句测试此触发器 .
这是我创建的触发器,但数据未显示在Products_Audit表中,它显示所有null .
USE my_guitar_shop;
DROP TRIGGER IF EXISTS products_after_update;
DELIMITER $$
CREATE TRIGGER products_after_update
BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
INSERT INTO products_audit (audit_id, product_id, category_id, product_code,
product_name, list_price, discount_percent, date_updated)
SELECT audit_id, products.product_id, products.category_id, products.product_code,
products.product_name,products.list_price, products.discount_percent, date_updated
FROM products JOIN products_audit
ON products_audit.audit_id = (SELECT audit_id FROM inserted);
END $$
DELIMITER ;
使用INSERT INTO编辑
USE my_guitar_shop;
DROP TRIGGER IF EXISTS products_after_update;
DELIMITER $$
CREATE TRIGGER products_after_update
BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
INSERT INTO products_audit (audit_id, product_id, category_id,product_code,
product_name, list_price, discount_percent, date_updated)
VALUES (OLD.audit_id, OLD.product_id, OLD.category_id, OLD.product_code,
OLD.product_name, OLD.list_price, OLD.discount_percent, OLD.date_updated)
DELIMITER ;
1 回答
你是插件过于复杂 . 正如triggers上的mysql文档所说:
因此,在插入中使用
OLD.column_name
格式 . 另外,我会将audit_id
字段设置为自动递增并将其从插入中删除: