mardi 1 mars 2016

SQLite Trigger syntax error at end of any query

I'm using Android's SQLite

My schema:

CREATE TABLE timecards (
 id INTEGER PRIMARY KEY,
 template_id INTEGER,
 autofill_id INTEGER,
 employee_name TEXT,
 production_name TEXT,
 week_ending TEXT,
 FOREIGN KEY(template_id) REFERENCES templates(id),
 FOREIGN KEY(autofill_id) REFERENCES autofills(id)
);

CREATE TABLE timecard_fields (
 id INTEGER PRIMARY KEY,
 timecard_id INTEGER,
 timecard_field_name TEXT,
 timecard_field_value TEXT,
 FOREIGN KEY(timecard_id) REFERENCES timecards(id)
);

CREATE TABLE autofills (
 id INTEGER PRIMARY KEY,
 template_id INTEGER,
 last_used TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE autofill_fields (
 id INTEGER PRIMARY KEY,
 autofill_id INTEGER,
 autofill_field_name TEXT,
 autofill_field_value TEXT,
 FOREIGN KEY(autofill_id) REFERENCES autofills(id)
);

CREATE TABLE templates (
 id INTEGER PRIMARY KEY,
 template_name TEXT
);

The trigger I'm trying to use:

CREATE TRIGGER timecard_employee_name_delete_trigger DELETE ON timecard_fields
WHEN OLD.timecard_field_name='employee_name'
BEGIN
    UPDATE timecards
         SET employee_name = (SELECT af.autofill_field_value FROM autofills af WHERE af.autofill_id = autofill_id AND af.autofill_field_name = 'employee_name')
         WHERE id=OLD.timecard_id;

    UPDATE timecards SET employee_name=NULL
        WHERE id=OLD.timecard_id;
END;

It is a complicated trigger, I know. The idea is to fill one table with a value from one of two other tables. However, I get the same syntax error when I substitute this trigger:

CREATE TRIGGER timecard_employee_name_delete_trigger DELETE ON timecard_fields
WHEN OLD.timecard_field_name='employee_name'
BEGIN
    INSERT INTO templates (template_name) VALUES('name');
END;

This error always appears on the first query inside the trigger:

(1) near ";": syntax error

The INSERT INTO query works fine outside of the trigger.

Any thoughts would be appreciated.

Aucun commentaire:

Enregistrer un commentaire