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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
