如何在Sql触发器中获取当前已删除行的值，并用已删除的行数据更新另一个表

Question

每当用户从表中删除数据时，我都会触发SQL触发器。我想在删除当前数据之前更新另一个表。我无法获取当前删除的行数据。如何在SQL触发器中获取当前已删除数据的内容。

CREATE TRIGGER `before_insert_money` BEFORE DELETE ON `payment`
FOR EACH ROW BEGIN
DECLARE TEMP INT;
SELECT   OLD.user_id  into TEMP      
FROM OLD;
UPDATE user set monthly_limit=TEMP where id=1;
END

Answer 1

这将按照您希望删除的内容进行操作：

SELECT * FROM NS_WORDS;
mani
nikhil
sugandh
mining
_lkdnsad
_lkdndsadnjas
_lk
_ja
_ls
_lsa
create table deleted_vals(a varchar(20));
CREATE OR REPLACE TRIGGER trigger_deleted_vals
BEFORE DELETE
ON NS_WORDS
FOR EACH ROW
DECLARE
BEGIN
INSERT INTO deleted_vals VALUES(:old.a);
END;
/
delete from NS_WORDS where rownum=1;
SELECT * FROM deleted_vals;
mani
for your table it would be like:
CREATE TRIGGER before_insert_money
BEFORE DELETE ON payment
FOR EACH ROW 
BEGIN
UPDATE user set monthly_limit=:old.user_id where id=1;
END;
/

Answer 2

您只需要引用OLD.userid，而不需要存储到另一个变量TEMP中。此外，没有可以在FROM子句中使用的OLD表。您只需要使用OLD.<column name>为要删除的行使用特定列的值。

DELIMITER $$
CREATE TRIGGER `before_insert_money` BEFORE DELETE ON `payment`
FOR EACH ROW BEGIN
UPDATE user set monthly_limit = OLD.user_id where id=1;
END $$
DELIMITER ;