MySQL and Python: Writing a script to create triggers and "NEW" keyword won't work - Stack Overflow

admin管理员组

文章数量:1401659

I am writing a script to dynamically create triggers on all of the tables in a database.

Everything was working until I tried to add in the MySQL keyword of "NEW" to log a change made. When I use the CREATE TRIGGER statement directly in MySQL Workbench, it works. When I run it through VS Code using Python3 MySQL Connector, I get an error of: Error: 1054 (42S22): Unknown column 'Id' in 'NEW'.

Now, before anybody suggests that the tables do not have an "Id" column, they do. That's the auto-incrementing primary key for every single table in the database. The problem here seems to be the wording. It is trying to find a table named NEW with a column named Id. THAT does not exist.

Has anybody had this issue and how have you gotten around it? I have asked ALL the AI bots and they say the same thing over and over again, that a table named "NEW" doesn't have the column named "Id" so I must create it or fix the code.

Let me reiterate that the CREATE TRIGGER statement works flawlessly if I input it directly into MySQL Workbench. That said, I REALLY don't want to have to make this trigger for every single table...

Here is the code:

        insert_trigger = f"""
        CREATE TRIGGER {trigger_prefix}_Insert
        AFTER INSERT ON {table}
        FOR EACH ROW
        BEGIN
            CALL LogActivity(NEW.Id, CURRENT_USER(), 'Insert', '{table}');
        END;
        """
        cursor.execute(insert_trigger)
        print(f"Created INSERT trigger for table {table}")

本文标签： MySQL and Python Writing a script to create triggers and quotNEWquot keyword won39t workStack Overflow