CREATETABLE Product (
Id INT(10)PRIMARYKEYAUTO_INCREMENT,
Name VARCHAR(20)NOTNULL,FunctionVARCHAR(50),
Company VARCHAR(20)NOTNULL,
Address VARCHAR(20));CREATETABLE Operate (
Op_id INT(10)PRIMARYKEYAUTO_INCREMENT,
Op_type VARCHAR(20)NOTNULL,
Op_time VARCHAR(20)NOTNULL);
创建 BEFORE INSERT、AFTER UPDATE 和 AFTER DELETE 触发器:
DELIMITER $$
CREATETRIGGER product_bf_insert
BEFORE INSERTON Product
FOR EACH ROWBEGININSERTINTO Operate (Op_type, Op_time)VALUES('INSERT',NOW());END$$
CREATETRIGGER product_af_update
AFTERUPDATEON Product
FOR EACH ROWBEGININSERTINTO Operate (Op_type, Op_time)VALUES('UPDATE',NOW());END$$
CREATETRIGGER product_af_del
AFTERDELETEON Product
FOR EACH ROWBEGININSERTINTO Operate (Op_type, Op_time)VALUES('DELETE',NOW());END$$
DELIMITER;
对 Product 表分别执行 INSERT、UPDATE 和 DELETE 操作:
-- 插入数据INSERTINTO Product (Name,Function, Company, Address)VALUES('Product1','Function1','Company1','Address1');-- 更新数据UPDATE Product
SET Name ='Product1_Updated'WHERE Id =1;-- 删除数据DELETEFROM Product
WHERE Id =1;