SQL trigger implementations

Question

I am trying to write a mySql trigger for a website database. The backend uses django framework.

def add_trigger():
    trigger = "CREATE TRIGGER update_ppl_rating \
    BEFORE INSERT ON demosite_peoplerating \
    FOR EACH ROW \
    BEGIN \
    DECLARE total integer; \
    DECLARE sum integer; \
    DECLARE avg float; \
    SELECT COUNT(*) INTO total FROM demosite_peoplerating GROUP BY apart_key HAVING apart_key = NEW.apart_key; \
    SELECT SUM(rating) INTO total FROM demosite_peoplerating GROUP BY apart_key HAVING apart_key = NEW.apart_key; \
    SET avg = sum/total; \
    UPDATE demosite_ratingtable \
    SET ppl_rating = avg \
    FROM demosite_ratingtable \
    WHERE apart_key = NEW.apart_key \
    END;"
    cursor = connection.cursor()
    cursor.execute(trigger)
    cursor.close()

Here is the database model related:

class RatingTable(models.Model):
    apart_key = models.ForeignKey(
        'ApartmentFeature',
        on_delete=models.CASCADE,
        unique=True,
        primary_key=True
    )
    env_rating = models.FloatField()
    ppl_rating = models.FloatField()
    rest_05_count = models.IntegerField()
    rest_1_count = models.IntegerField()
    rest_2_count = models.IntegerField()
    shop_05_count = models.IntegerField()
    shop_1_count = models.IntegerField()
    shop_2_count = models.IntegerField()

class PeopleRating(models.Model):
    comment_id = models.AutoField(primary_key=True)
    apart_key = models.ForeignKey(
        'ApartmentFeature',
        on_delete=models.CASCADE,
    )
    rating = models.IntegerField()
    comment = models.CharField(max_length=200)
    nick_name = models.CharField(max_length=200)

After running add_trigger(), mysql server replies:

django.db.utils.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM demosite_ratingtable     WHERE apart_key = NEW.apart_key     END' at line 1")

Could you please any one give me tha idea to solve this?

you're posting a question about MySQL but all your examples are in Python. You should provide clear trigger code and DB schema. — Stalinko, Nov 21 '19 at 06:34
In a trigger, you cannot update a different row than the one you are currently inserting. So even if you would fix your update statement (not `update ... set ... **from** ... where ... `, but `update tablename set ... where ...`), you would get [MySql Error: Can't update table in stored function/trigger because it is already used by statement which invoked this stored function/trigger](https://stackoverflow.com/q/15300673). You may need to reorganize your data (e.g. store totals per group in a separate table), do the update in another query, dont store it but calculate it when you need it, ... — Solarflare, Nov 21 '19 at 10:46

SQL trigger implementations

0 Answers0