[Apparently I don't get my own postings to this list?]
Anyway, I have an improvement to my trigger that eliminates the SELECT's for
event_id: two triggers! Comments welcome.
-- First set the SET's correlation id when a CLR is inserted
CREATE TRIGGER set_event_correlation_id_on_insert
AFTER INSERT ON device_event
WHEN new.event_state = 1
BEGIN
UPDATE device_event SET event_correlation_id = new.event_id
WHERE device_id = new.device_id
AND event_code = new.event_code
AND event_state = 2
AND event_correlation_id IS NULL
ORDER BY event_date DESC LIMIT 1;
END;
-- Then trigger on that SET's update to set the CLR's correlation id
CREATE TRIGGER set_event_correlation_id_on_update
AFTER UPDATE OF event_correlation_id ON device_event
WHEN new.event_state = 2
AND new.event_correlation_id IS NOT NULL
BEGIN
UPDATE device_event SET event_correlation_id = new.event_id
WHERE event_id = new.event_correlation_id;
END;
Chris
On Mon, Nov 17, 2008 at 9:11 AM, Chris Eich <[EMAIL PROTECTED]> wrote:
> [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
