第七次作业

1. 创建 Product 和 Operate 表：

CREATE TABLE Product (
    Id INT(10) PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(20) NOT NULL,
    Function VARCHAR(50),
    Company VARCHAR(20) NOT NULL,
    Address VARCHAR(20)
);

CREATE TABLE Operate (
    Op_id INT(10) PRIMARY KEY AUTO_INCREMENT,
    Op_type VARCHAR(20) NOT NULL,
    Op_time VARCHAR(20) NOT NULL
);

2. 创建 BEFORE INSERT、AFTER UPDATE 和 AFTER DELETE 触发器：

DELIMITER $$

CREATE TRIGGER product_bf_insert
BEFORE INSERT ON Product
FOR EACH ROW
BEGIN
    INSERT INTO Operate (Op_type, Op_time) 
    VALUES ('INSERT', NOW());
END$$

CREATE TRIGGER product_af_update
AFTER UPDATE ON Product
FOR EACH ROW
BEGIN
    INSERT INTO Operate (Op_type, Op_time) 
    VALUES ('UPDATE', NOW());
END$$

CREATE TRIGGER product_af_del
AFTER DELETE ON Product
FOR EACH ROW
BEGIN
    INSERT INTO Operate (Op_type, Op_time) 
    VALUES ('DELETE', NOW());
END$$

DELIMITER ;

3. 对 Product 表分别执行 INSERT、UPDATE 和 DELETE 操作：

-- 插入数据
INSERT INTO Product (Name, Function, Company, Address) 
VALUES ('Product1', 'Function1', 'Company1', 'Address1');

-- 更新数据
UPDATE Product 
SET Name = 'Product1_Updated' 
WHERE Id = 1;

-- 删除数据
DELETE FROM Product 
WHERE Id = 1;

4. 删除 product_bf_insert 和 product_af_update 触发器：

DROP TRIGGER IF EXISTS product_bf_insert;
DROP TRIGGER IF EXISTS product_af_update;