Hi Sam, Brainstorming your suggestion a bit, rather than allowing triggers to function across databases (which is understandably not practical given the architecture), perhaps there could be a way to define a "partition" within a database (similar to creating a folder on a drive). Borrowing from Oracle, maybe this could be called a "Schema", and a "create schema" command could be used to create it. Queries referencing a table in a different schema must preface the table name with the schema name - e.g., "SELECT * FROM Sam.users".
Just some rainy day ideas.. - Jeff -----Original Message----- From: Samuel R. Neff [mailto:[EMAIL PROTECTED] Sent: Thursday, January 24, 2008 10:59 AM To: sqlite-users@sqlite.org Subject: RE: [sqlite] Foreign Constraint Triggers Across Attached Databases I've run into two situations recently where I would have preferred to write triggers across databases. Both related to audit tracking of data. The first situation is that for every table, I have a corresponding history table that records the history of every record. So let's say I have CREATE TABLE Users (UserID, UserName); then I also have CREATE TABLE Users_History (UserHistoryID, TransactionID, ChangeType, UserID, UserName) and to track transactions I have CREATE TABLE Transactions(TransactionID, TrasnactionTS, UserID) and to track the single active transaction I have CREATE TABLE ActiveTransaction(TransactionID) which is always blank except when in the middle of a transaction (which always starts with inserting a record to that table, and then deleting it right before commit). So I have triggers on the Users table that whenever a record is inserted, updated, or deleted, the corresponding new values for insert/update and old values for delete are inserted into the history table. The triggers look like this: CREATE TRIGGER HI_Users AFTER INSERT ON Users FOR EACH ROW BEGIN SELECT RAISE(ABORT, 'Can not update database when no transaction is active. Create a new transaction in the Transactions table and create an associated record in the ActiveTransaction table.') WHERE (SELECT COUNT(*) FROM ActiveTransaction) = 0; INSERT INTO Users_History ( TransactionID, ChangeType, UserID, UserName ) SELECT (SELECT MIN(TransactionID) FROM ActiveTransaction), 'I', NEW.UserID, NEW.UserName ; END; Due to the restriction that triggers cannot span databases, I have my main data tables, history tables, and the ActiveTransaction table all in the same database. I'd really rather the history tables be in a separate database because they can grow quite large and when I ask a customer to e-mail me their database, I'd like them to be able to easily e-mail the main data only without the extra history info. Also, it would be much cleaner if the ActiveTransaction table was in TEMP instead of in MAIN so each connection clearly has it's own table (except where now they share the same table definition, just the data is never shared due to convention of being populated only within a transaction). I hope these examples are helpful. I would like to see the ability to create a trigger that spans database some day and would expect that the trigger could be defined and simply would error out if at runtime the required database was not present. Thanks, Sam ------------------------------------------- We're Hiring! Seeking a passionate developer to join our team building Flex based products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] -----Original Message----- From: D. Richard Hipp [mailto:[EMAIL PROTECTED] Sent: Thursday, January 24, 2008 6:56 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Foreign Constraint Triggers Across Attached Databases Triggers between two separate databases are not allowed since if you DETACH one of the databases, the triggers obviously will no longer work. If two separate databases are so inseparably bound that they need triggers between them, why not just make them a single database? The same goes for foreign key constraints. There are *severe* implementation difficulties trying to get this to work across separate database. If you have a foreign key in a separate database, that really argues that the two databases ought to be one. ------------------------------------------------------------------------ ----- To unsubscribe, send email to [EMAIL PROTECTED] ------------------------------------------------------------------------ ----- ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
