我希望在特定表被删除之前跟踪这些记录。我正在使用MySQL运行一条SQL触发器语句,它运行成功,但是我删除的行没有被插入到表中。
CREATE TABLE `history_tblvendorpayment` LIKE `tblvendorpayment`;
ALTER TABLE `history_tblvendorpayment`
MODIFY COLUMN `id` INT UNSIGNED NOT NULL;
ALTER TABLE `history_tblvendorpayment` DROP PRIMARY KEY;
ALTER TABLE `history_tblvendorpayment`
ADD COLUMN `history_id` INT UNSIGNED NOT NULL;
ALTER TABLE `history_tblvendorpayment`
ADD CONSTRAINT PRIMARY KEY (`history_id`);
ALTER TABLE `history_tblvendorpayment`
MODIFY `history_id` INT UNSIGNED NOT NULL AUTO_INCREMENT;
DELIMITER $$
DROP TRIGGER `delete_tblvendorpayment`$$
CREATE TRIGGER `delete_tblvendorpayment` BEFORE DELETE on `tblvendorpayment`
FOR EACH ROW
BEGIN
INSERT INTO history_tblvendorpayment (id, created_by,
vendor_id, amount_payable,
amount_payed, description,
depositor_name, gl_code_credit,
gl_code_debit, txn_date,
pay_date, ref_id,
bank, currency,
date, md_approval,
status, sage_status,
memo_id)
VALUES (
OLD.id, OLD.created_by,
OLD.vendor_id, OLD.amount_payable,
OLD.amount_payed, OLD.description,
OLD.depositor_name, OLD.gl_code_credit,
OLD.gl_code_debit, OLD.txn_date,
OLD.pay_date, OLD.ref_id,
OLD.bank, OLD.currency,
OLD.date, OLD.md_approval,
OLD.status, OLD.sage_status,
OLD.memo_id
);END$$
DELIMITER ;发布于 2019-04-15 21:15:34
然后试试这个,虽然你的代码看起来很好,但是这个更短
CREATE TABLE `history_tblvendorpayment` LIKE `tblvendorpayment`;
ALTER TABLE `history_tblvendorpayment`
MODIFY COLUMN `id` INT UNSIGNED NOT NULL;
ALTER TABLE `history_tblvendorpayment` DROP PRIMARY KEY;
DELIMITER $$
CREATE TRIGGER `delete_tblvendorpayment` BEFORE DELETE on `tblvendorpayment`
FOR EACH ROW
BEGIN
INSERT INTO history_tblvendorpayment
SELECT *
FROM tblvendorpayment
WHERE id = OLD.id;
END$$
DELIMITER ;https://stackoverflow.com/questions/55590991
复制相似问题