In my next post I'll post a [681 line, 28KB unified diff, or 504 line regular diff] patch implements the following DB triggers:
- AFTER DATABASE CONNECT - AFTER TRANSACTION BEGIN - BEFORE TRANSACTION COMMIT These triggers do exactly what I want, and nothing more. If anyone wants to test this go ahead (well, wait for the patch); feedback will be greatly appreciated. The following DB triggers are parsed but not yet supported, and yield a not supported error: - BEFORE DATABASE DISCONNECT - AFTER TRANSACTION COMMIT - AFTER TRANSACTION ROLLBACK I'd like to get these last three to work eventually. The connect trigger only fires after executing "PRAGMA connect_triggers=on". I've not yet added a compile-time option to enable connect triggers by default. Here's a schema for a demonstration: sqlite> select sql || ';' from sqlite_master; CREATE TABLE t(a); CREATE VIEW sqlite_intview_begin AS SELECT 0 AS nothing; CREATE VIEW sqlite_intview_bcommit AS SELECT 0 AS nothing; CREATE TRIGGER ab after transaction begin begin insert into t select 'transaction started at ' || datetime('now'); end; CREATE TRIGGER bc before transaction commit begin select raise(abort, 'Foo!') where (select max(a) from t where typeof(a) = 'integer') > 567; select raise(fail, 'Bar!') where (select max(a) from t where typeof(a) = 'integer') = 567; select raise(rollback, 'FooBar!') where (select max(a) from t where typeof(a) = 'integer') = 566; insert into t select 'transaction started at ' || datetime('now'); end; CREATE VIEW sqlite_intview_connect AS SELECT 0 AS nothing; CREATE TRIGGER adc after database connect begin insert into t select 'connected at ' || datetime('now'); end; sqlite> And a quick demo. 1) Begin and commit triggers: sqlite> begin; sqlite> select * from t; sqlite> insert into t values (123); sqlite> select * from t; transaction started at 2011-05-16 17:18:30 123 sqlite> insert into t values (234); sqlite> select * from t; transaction started at 2011-05-16 17:18:30 123 234 sqlite> rollback; sqlite> select * from t; sqlite> begin; sqlite> insert into t values (123); sqlite> insert into t values (234); sqlite> commit; sqlite> select * from t; transaction started at 2011-05-16 17:18:48 123 234 transaction started at 2011-05-16 17:18:52 sqlite> 2) Commit trigger that raises ABORT or FAIL: sqlite> delete from t; sqlite> select * from t; sqlite> begin; sqlite> insert into t values (123); sqlite> insert into t values (234); sqlite> insert into t values (568); sqlite> commit; Error: Foo! sqlite> select * from t; 123 234 568 sqlite> rollback; sqlite> sqlite> begin; sqlite> insert into t values (123); sqlite> insert into t values (234); sqlite> insert into t values (567); sqlite> commit; Error: Bar! sqlite> select * from t; transaction started at 2011-05-16 17:23:15 123 234 567 sqlite> rollback; sqlite> select * from t; sqlite> sqlite> begin; sqlite> insert into t values (123); sqlite> insert into t values (234); sqlite> insert into t values (567); sqlite> commit; Error: Bar! sqlite> select * from t; transaction started at 2011-05-16 17:23:15 123 234 567 sqlite> rollback; sqlite> select * from t; sqlite> 3) Commit trigger that raises ROLLBACK: sqlite> select * from t; sqlite> begin; sqlite> insert into t values (123); sqlite> insert into t values (234); sqlite> insert into t values (566); sqlite> select * from t; transaction started at 2011-05-16 17:24:44 123 234 566 sqlite> commit; Error: FooBar! sqlite> select * from t; sqlite> sqlite> sqlite> rollback; Error: cannot rollback - no transaction is active sqlite> 4) Connect trigger: sqlite> select * from t; sqlite> pragma connect_triggers=on; sqlite> select * from t; transaction started at 2011-05-16 17:26:07 connected at 2011-05-16 17:26:07 transaction started at 2011-05-16 17:26:07 sqlite> sqlite> rollback; Error: cannot rollback - no transaction is active sqlite> Nico -- _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users