[Sorry if you see this twice; I sent it yesterday and see it in the archives, but never got it in my inbox. --Chris]
On Sun, Nov 16, 2008 at 12:59 PM, Chris Eich <[EMAIL PROTECTED]> wrote: > 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 > new record 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
