删除记录备份触发器

删除记录备份触发器

很多时候我们需要对表进行删除操作，但为了防止误操作或被攻击导致数据丢失，需要记录被删除的记录。系统会将删除的记录存放在 deleted 表中。

简单查看被删除的记录

CREATE TRIGGER Trig_Delete_Student
ON Student
AFTER DELETE
AS 
BEGIN
    SELECT * FROM deleted
ENDCode language: PHP (php)

将被删除的记录保存到另外一张表

CREATE TRIGGER Trig_Delete_Student
ON Student
AFTER DELETE
AS 
BEGIN
    INSERT INTO [dbo].[Student_History]
           ([OldID]
           ,[Name]
           ,[ClassID]
           ,[AddTime]
           ,[Status]
           ,[DeleteTime])
    SELECT ID, Name, ClassID, AddTime, [Status], GETDATE()
    FROM deleted
ENDCode language: CSS (css)

测试删除并验证

DELETE FROM Student
WHERE ID = 3

SELECT * FROM Student_History

通过触发器，每次删除 Student 表中的记录时，被删除的数据会自动备份到 Student_History 表中，包含删除时间，便于后续追溯和恢复。