In my little test below, both the "ON DELETE CASCADE" in table_2 and the
(later created) delete_trigger_2 are about to execute removal when a row
in table_1 is removed.
The table_3 also has the "ON DELETE CASCADE", but it doesn't yet have
any content when removing the row in table_1 so it's initially unaffected.
The row in table_3 gets created when the "ON DELETE CASCADE" in table_2
executes when deleting the row in table_1.
I actually don't need the delete_trigger to fire if the delete was
casued by ON DELETE CASCADE (only need to execute when the delete is
made directly in the table_2 table).
I wonder if the tables cleared from an ON DELETE CASCADE and manually
created triggers always execute in a predetermined order?
(If so I could solve my issue by creating a trigger like
"delete_trigger_2" in the test since it (in my environment) gets
executed after the row in table_3 gets inserted).
Here's a little test which can be run in the sqlite shell to test the
behaviour I'm interested in:
PRAGMA FOREIGN_KEYS = ON;
CREATE TABLE table_1(
id INTEGER PRIMARY KEY
);
CREATE TABLE table_2 (
table_1_id INT NOT NULL,
starttime DATETIME NOT NULL,
CONSTRAINT 'FK_table_2' FOREIGN KEY(table_1_id) REFERENCES table_1(
id ) ON DELETE CASCADE
);
CREATE TABLE table_3 (
table_1_id INTEGER PRIMARY KEY,
endtime DATETIME NOT NULL,
CONSTRAINT 'FK_table_3' FOREIGN KEY( table_1_id ) REFERENCES
table_1 ( id ) ON DELETE CASCADE
);
CREATE TRIGGER insert_trigger AFTER INSERT ON table_2
BEGIN
DELETE FROM table_3 WHERE table_1_id = new.table_1_id;
END;
CREATE TRIGGER delete_trigger AFTER DELETE ON table_2
BEGIN
INSERT INTO table_3 (table_1_id, endtime) VALUES (old.table_1_id,
strftime('%Y-%m-%d %H:%M:%S', 'now', 'localtime'));
END;
--testing
INSERT INTO table_1 VALUES(1);
INSERT INTO table_2 VALUES(1, strftime('%Y-%m-%d %H:%M:%S', 'now',
'localtime'));
-- the below delete violates a constraint since deleting the row in table_1
-- will execute the ON DELETE CASCADE in table_2, which in turn executes
-- the delete_trigger, which creates a row in table_3 referencing table_1
DELETE FROM table_1;
-- To solve this we can create a trigger which deletes newly created
-- content in table_3 after we've deleted a row in table_1.
-- BUT.. Is it certain this always will be run AFTER the delete_trigger ?
CREATE TRIGGER delete_trigger_2 AFTER DELETE ON table_1
BEGIN
DELETE FROM table_3 WHERE table_1_id = old.id;
END;
--New test with the delete_trigger_2 also active
DELETE FROM table_1;
--Should work
...Or if there is a more elegant solution I haven't thought about?
/Daniel
