I have an events table where SET events for a given device_id and event_code
are followed by CLR events.
CREATE TABLE device_event (
event_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
device_id INTEGER NOT NULL REFERENCES device,
event_date INTEGER NOT NULL,
event_code INTEGER NOT NULL REFERENCES event_code,
event_state INTEGER NOT NULL REFERENCES event_state,
event_text TEXT,
exported INTEGER DEFAULT 0,
created_date INTEGER NOT NULL,
event_correlation_id INTEGER
);
I want to set event_correlation_id when a CLR is inserted, so that the SET
and CLR events reference each other.
CREATE TRIGGER update_event_correlation_id AFTER INSERT ON device_event
WHEN new.event_state = 1
BEGIN
UPDATE device_event SET event_correlation_id = (
SELECT event_id FROM device_event
WHERE device_id = new.device_id
AND event_code = new.event_code
AND event_state = 2
ORDER BY event_date DESC LIMIT 1
) WHERE event_id = new.event_id;
UPDATE device_event SET event_correlation_id = new.event_id
WHERE event_id = new.event_correlation_id;
END;
My first version of this trigger failed entirely when I omitted the AFTER
keyword. I wish the default *trigger-time* were documented.
The second UPDATE in the trigger still fails, though; it seems that
the newrecord does not get modified by the first UPDATE. If this is
true, perhaps
it could also be documented.
I ended up having to run the SELECT a second time to make the second UPDATE
work:
CREATE TRIGGER update_event_correlation_id AFTER INSERT ON device_event
WHEN new.event_state = 1
BEGIN
UPDATE device_event SET event_correlation_id = (
SELECT event_id FROM device_event
WHERE device_id = new.device_id
AND event_code = new.event_code
AND event_state = 2
ORDER BY event_date DESC LIMIT 1
) WHERE event_id = new.event_id;
UPDATE device_event SET event_correlation_id = new.event_id
WHERE event_id = (
SELECT event_id FROM device_event
WHERE device_id = new.device_id
AND event_code = new.event_code
AND event_state = 2
ORDER BY event_date DESC LIMIT 1
);
END;
I am afraid this will get too slow, and can do without the second update
(setting event_correlation_id for the earlier SET event), but I would like
to know if there's a more efficient way to do both.
Chris
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
