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