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
