create table branch_audit
(br_branchno varchar(4),
br_branchname varchar(20),
br_street varchar(40) not null unique,
br_city varchar(20),
br_state varchar(2),
br_zipcode varchar(5),
br_phone varchar(10),
br_fax varchar(10),
br_per_mgt_fee decimal(5,2),
br_email varchar(25),
constraint br_primarykey primary key (br_branchno));
上面一行创建了与"分支"表相同模式的审计表,以便它可以捕获所有已删除的旧数据
这是触发语句,非常基本的形式,我想知道是否有一种方法可以保持它的简单性:
CREATE TRIGGER trigger_deleteon
AFTER DELETE
ON branch
FOR EACH ROW
INSERT INTO branch_audit VALUES(select * from branch);
DELETE FROM Branch WHERE CL_CLIENTNO = 'B001';
我将尽力在这里解释清楚。因此,我希望代码在触发器被激活后获取已删除的值,然后获取已删除的元组(行)并将其存储在我创建的审计表中,以便跟踪对DB的修改。
上面带下划线的语句是必须更改的语句,以捕获已删除的元组,目前它捕获每个元组,而不是只捕获更新的元组。
可以通过MySQL触发器中的OLD
关键字访问已删除行的值。下面的内容应该可以工作,其中...
将被替换为所有其他属性。
CREATE TRIGGER trigger_deletion
BEFORE DELETE ON branch
FOR EACH ROW
BEGIN
INSERT INTO branch_audit VALUES(OLD.br_branchno,
OLD.br_branchname, OLD.br_street, ...);
END;
可以用以下方式使用BEFORE DELETE
触发器代替AFTER DELETE
触发器
CREATE TRIGGER trigger_deleteon
BEFORE DELETE ON branch
FOR EACH ROW
INSERT INTO branch_audit
SELECT *
FROM branch
WHERE br_branchno = OLD.br_branchno;
这里是SQLFiddle demo