Hi, How to write a trigger so that it will log the updates on all tables in database into a auditlog tables?. For Example: The database contains 3 tables 1)country 2) state 3) auditlog List of fields on each table ---------------------------- Country table: Cid name
Sate table: Sid name countryid Auditlog table: Aid TableName GUID ChangedDate DataChanged Action Tablename: is the name of the table the action(insert/update/delete) has performed Guid: is the primary key value of the table DataChanged: contains the xml in string format. Ex:If an operation is performed on Country table Insert into country(cid, name) values (1, india) Then the xml format will be: <Country> <Cid>1</Cid> <name>India></name> </Country> I need a trigger in such a way that it will log all the actions performed on country & state tables into the auditlog table. Please help me in writing the trigger for the above scenario and also provide example if possible Regards, Nirmala. -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of ???????? ?????????? Sent: Tuesday, July 26, 2011 6:04 PM To: Black, Michael (IS) Cc: General Discussion of SQLite Database Subject: Re: [sqlite] SELECT query first run is VERY slow > Could you post your timings and read stats again? Are you happy with what > you're seeing now? > Actually, there was ~ 50% speed-up. More or less. The idea of normalizing worked great, thank you. I'm concerned about SQLITE indexes. > > > I think when you drop the index and recreate it still scatters. But re-doing > your data with the new kind will tell for sure. I take it you have to build > the index while running and can't wait until it's done. To me problem is narrowed to the fact that SQLITE is ignoring ROWID value while scanning index. Using "INDEX ON Foo(Bar)" and condtion like "Bar = XXX and Rowid > YYY" SQLITE reads too much. Looks like in this case SQLITE reads all of index records matching condition Bar = XXX and scan them. And if Bar has only a few allowed values (= bad selectivity ) this takes lot's of time and disk i/o cause there are lots of records with the same Bar value. Looks like SQLITE is not using knowledge that index records are sorted by Bar and then by Rowid. This is something I don't (and didn't) expect... > > > > > > Michael D. Black > > Senior Scientist > > NG Information Systems > > Advanced Analytics Directorate > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Please do not print this email unless it is absolutely necessary. The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users