在MySQL中执行DELETE操作后触发触发器
执行DELETE操作后，使用AFTERDELETE触发触发器。以下是语法-
DELIMITER //
CREATE TRIGGER yourTriggerName
AFTER DELETE
ON yourTableName FOR EACH ROW
BEGIN
yourStatement1,
.
.
N
END;
//让我们首先创建一个表-
mysql> create table DemoTable(Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,FirstName varchar(100));
使用插入命令在表中插入一些记录-
mysql> insert into DemoTable(FirstName) values('John');
mysql> insert into DemoTable(FirstName) values('Mike');使用select语句显示表中的所有记录-
mysql> select *from DemoTable;
这将产生以下输出-
+----+-----------+ | Id | FirstName | +----+-----------+ | 1 | John | | 2 | Mike | +----+-----------+ 2 rows in set (0.00 sec)
以下是创建触发器并使用AFTERDELETE的查询-
mysql> DELIMITER //
mysql> CREATE TRIGGER history_of_Table
AFTER DELETE
ON DemoTable FOR EACH ROW
BEGIN
SELECT USER() INTO @userName;
SELECT NOW() INTO @deleteDatetime;
END; //
mysql> DELIMITER ;要检查触发器是否正常工作，请从表中删除第一条记录：
mysql> delete from DemoTable where Id=1;
上面，我们从表中删除了第一条记录。这意味着使用DELETE命令后触发器必须处于活动状态-
mysql> select @userName;
这将产生以下输出-
+----------------+ | @userName | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec)
每当触发器起作用时，它将显示当前时间-
mysql> select @deleteDatetime;
这将产生以下输出-
+---------------------+ | @deleteDatetime | +---------------------+ | 2019-07-09 21:06:31 | +---------------------+ 1 row in set (0.00 sec)