快速 MySQL 触发器更新 [英] Quick MySQL Trigger Update

问题描述

我需要更改此触发器以指向刚刚更新的列，那么我应该将 NEW.song_id 更改为什么?谢谢！

I need to change this trigger to point to the column that just got updated so what do I change NEW.song_id to?? Thanks!

DROP TRIGGER IF EXISTS `ratings_update`//
CREATE TRIGGER `ratings_update` AFTER UPDATE ON `ratings2`
FOR EACH ROW update SONGS set
rating_sum = rating_sum + NEW.rating,
rating = rating_sum / rating_count
where id = NEW.song_id
//

编辑 vvv

DROP TRIGGER IF EXISTS `ratings_upd`//
CREATE TRIGGER `ratings_upd` AFTER UPDATE ON `ratings2`
FOR EACH ROW update SONGS set
rating_sum = rating_sum + NEW.rating - OLD.rating,
rating_count = rating_count,
rating = rating_sum / rating_count
where id = NEW.song_id
//

这是我为我的触发器设计的，但是当我运行它时它抛出了这个错误..

This is what I came up with for my trigger but it is throwing me this error when I run it..

推荐答案

OLD 访问更新前的值，NEW 访问更新后的值.

OLD accesses values before the update, and NEW accesses values after the update.

来自 http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html:

您可以使用别名 OLD 和 NEW 来引用主题表(与触发器关联的表)中的列.OLD.col_name 在更新或删除之前引用现有行的列.NEW.col_name 指的是要插入的新行或更新后的现有行的列.

You can refer to columns in the subject table (the table associated with the trigger) by using the aliases OLD and NEW. OLD.col_name refers to a column of an existing row before it is updated or deleted. NEW.col_name refers to the column of a new row to be inserted or an existing row after it is updated.

这篇关于快速 MySQL 触发器更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！