On Mon, 15 Mar 2010 18:52:26 -0400, andy <andrew.g.b...@gmail.com> wrote:
>Hello >I am hoping someone can help with the following problem. > >- I want to create a log of all changes to tables in a database. >- I would like to keep the log table in a separate database to the >main data files >- I plan to use triggers to capture changes and insert them into the >log table. > >Question: >How can I embed the 'attach" command within the trigger statement so >that the log database is attached if it is not already available? You can't do that. Even if you make sure the log database is attached at all times, it is not possible to define triggers (or views, for that matter) that span both databases. The reason for this design is that the schema would be invalid when one database file would be missing. Your best bet is to create the log table(s) in the same database, so it would be in the same ACID domain, consistent with the contents of the database, and periodically offload rows from the log table to an attached database, and delete them from the log table in the main database. Pseudo code (untested): ATTACH DATABASE 'log.db' as logdb; BEGIN EXCLUSIVE; INSERT INTO logdb.log (col1,col2,..) SELECT col1,col2,.. FROM log WHERE something; DELETE FROM log WHERE something; COMMIT; DETACH DATABASE logdb; >i.e something like > >create trigger insert_data after insert on data >begin > if database logdb does not exist > attach logdb.db as logdb; > > insert into logdb.log values(.....) >end; > > >I am also a little concerned about performance so I am not sure if >testing for the presence of the database for every change will add to >much overhead. Thoughts? Don't be concerned about performance until you really have a performance problem. A healthy schema usually performs well. Design, Build, Benchmark, Optimize, Benchmark. >Thanks -- ( Kees Nuyt ) c[_] _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
