1

I'm attempting to start off a database (testing with sqlite3), but I seem to be unable to do a SET inside a trigger. I get the following error: OperationalError: near "SET": syntax error.

An insert from inside the trigger works, so I'm just curious what I'm doing wrong here?

import sqlite3
conn = sqlite3.connect(':memory:')

c = conn.cursor()

c.execute("""CREATE TABLE Programs (
    id INTEGER PRIMARY KEY,
    name VARCHAR(64) NOT NULL,
    time_added INTEGER
);""")

c.execute("""CREATE TRIGGER program_time_added AFTER INSERT ON Programs
    FOR EACH ROW
    BEGIN
        SET new.time_added = UNIX_TIMESTAMP(NOW());
    END;""")

c.execute('INSERT INTO Programs (name) VALUES (?)', ['name'])
Peter
  • 3,186
  • 3
  • 26
  • 59
  • You're missing an actual statement to execute. https://www.sqlite.org/lang_createtrigger.html – Shawn Oct 22 '18 at 18:18
  • I know it's possible on mysql (https://stackoverflow.com/a/12936091/2403000), is it not permitted on sqlite? It seems a bit messy having to query the entire table just to get the inserted row (which it already knows). I was kind of hoping to be using sqlite as a placeholder for mysql, seems a bit annoying that they've removed some functionality – Peter Oct 22 '18 at 23:40
  • See the documentation I linked for the proper sqlite syntax. – Shawn Oct 22 '18 at 23:55
  • I was just under the impression all SQL was the same, I'll have to make a mental note on which bits need cleaning up later, just seems a little inefficient to have to do `update table set x=y where id = new.id` when both the table and row are already known :P – Peter Oct 23 '18 at 00:06
  • There are as many flavors of SQL as there are database programs. – Shawn Oct 23 '18 at 00:35
  • Ah alright thanks for the info. I've only ever used mysql (never been a fan though), fortunately I'm not going too much in depth so the rest should be pretty straight forward :) – Peter Oct 23 '18 at 01:11

0 Answers0