What would be a good way to extract metadata information about temporary triggers? If there are multiple tables with the same name in main and temp databases (or even attached databases), I could not find a way to determine on which table a temporary trigger was created by examining the available information in sqlite_temp_master, unless the table name was qualified in the trigger creation SQL.
Example: The main and temp databases both have a table called "t1". Querying sqlite_temp_master by type 'trigger' returns a trigger called "tr1" with the DDL: CREATE TRIGGER tr1 after insert on t1 begin select 1, 2, 3; end Was the trigger created on temp.t1 or on main.t1? After experimenting different scenarios - and applying the changes in the checkin [56dca4a65c <http://www.sqlite.org/src/info/56dca4a65c>] without which the first 2 test cases below are not even possible - I found out that the table on which the trigger is created depends on the order of creation of the objects. Consider the following test cases - the only difference between them is the order of the first 3 lines: Test case 1: create table main.t1(c); create temporary trigger tr1 after insert on t1 begin select raise(abort, 'error'); end; create table temp.t1(c); insert into main.t1(c) values(1); -- error is raised here, confirming the trigger was created on main.t1 insert into temp.t1(c) values(1); Note: without applying checkin [56dca4a65c<http://www.sqlite.org/src/info/56dca4a65c>], a "malformed database schema error" occurs at line 3. Test case 2: create table temp.t1(c); create temporary trigger tr1 after insert on t1 begin select raise(abort, 'error'); end; create table main.t1(c); insert into main.t1(c) values(1); insert into temp.t1(c) values(1); -- error is raised here, confirming the trigger was created on temp.t1 Test case 3: create table main.t1(c); create table temp.t1(c); create temporary trigger tr1 after insert on t1 begin select raise(abort, 'error'); end; insert into main.t1(c) values(1); insert into temp.t1(c) values(1); -- error is raised here, confirming the trigger was created on temp.t1 However, all three cases above produce the same trigger DDL in sqlite_temp_master: type name tbl_name rootpage sql ------- ---- -------- -------- ----------------------------------------------------------------------------- trigger tr1 t1 0 CREATE TRIGGER tr1 after insert on t1 begin select raise(abort, 'error'); end Unless I missed something, the information in sqlite_temp_master doesn't seem to provide useful clues about the table the trigger was created on. Is there another way? And yes, I know these scenarios can be avoided simply by qualifying the table name in the trigger declaration - which would change the DDL to "CREATE TRIGGER tr1 after insert on <db>.t1..." - but that is beside the point. It is not always possible to be in control of the queries the users may be executing. Perhaps it's time to introduce a "pragma trigger_info()"? :) Thank you for any insights on this issue. Bogdan Ureche _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
