A small correction: I was wrong about SQLite not supporting cascading
triggers. Cascading triggers are supported, recursive triggers are not.
That is, if you have an insert trigger on table A which, say, inserts
into table B, and there's an insert trigger on table B, it will run. But
if this latter trigger turns around and inserts into table A, the A
trigger won't run again.
Igor Tandetnik
Ran <[EMAIL PROTECTED]> wrote:
See the email of Igor Tandetnik from 18-Dec-2005:
"Vishal Kashyap" wrote
Is their any way we can write simple stored procedures or functions
in sqlite. If yes please do guide me I need this functionality in one
of my open source project.
Not in the usual sense, meaning some language that gets stored in the
database itself together with the data. The only thing that comes
somewhat close is a trigger. It is possible to create a poor man's
stored procedure like this:
create table sp_dosomething (param1 int, param2 char);
create trigger sp_dosomething_impl
instead of insert on sp_dosomething
begin
-- one or more sql statements possibly referring to
-- new.param1 and new.param2
end;
-- To invoke:
insert into sp_dosomething values(1, 'hello');
Note that triggers are rather limited in what they can do. They are
just
a bunch of SQL statements, there is no control flow (loops, if then
else, goto) beyond what little you can implement in pure SQL. They
cannot return values, except indirectly by inserting or updating some
table. SQLite does not support cascading triggers, so if your "stored
procedure" manipulates some table to which regular triggers are
attached
(perhaps ensuring data integrity), those triggers won't run.
SQLite supports custom functions - see sqlite3_create_function[16].
You
write them in C (or any other language that has bindings to SQLite
API)
and you have to install them every time you open a DB handle with
sqlite3_open, before you can refer to them in your SQL statements.
They
are not stored in the database file itself.
Finally, SQLite prepared statements (sqlite_prepare) can be thought of
as simple stored procedures defined in your program. Similar to custom
functions, you can prepare a statement right after opening the
database,
then keep it around.
Igor Tandetnik
Ran
On 3/24/06, Chethana, Rao (IE10)
<[EMAIL PROTECTED]> wrote:
Hi,
Can you tell me how to create a stored procedure in an sqlite3
database and use the same in a trigger? Please provide an example
(as complete as possible). In the stored procedure I need to execute
few queries on some tables. Can you tell me how to do that also?
Any help is deeply appreciated.
Best Regards,
Chethana
