My problem is not with cascading or using recursive triggers.
Actually,
I am using "sqlite3_create_function" to execute some queries(like
insert or update etc.) but the control does not pass to the custom
function at all, ie., say a user-defined function
sp_dosomethingfunc(sqlite3_context *context, int argc,sqlite3_value
**argv)
{
Do something or execute some queries.......
..................................etc
}
/* from main() I'm calling the above function using
sqlite3_create_function*/
int main()
{
/* after using sqlite_open */
sqlite3_create_function(............)
/* exec function is executed, but control is
not passed to sp_dosomethingfunc, how do I make stmts inside this custom
function get executed? */
sqlite3_exec(.........)
}
/* and this sp_dosomethingfunc, I'm calling from triggers. I'm not
getting any error. But this function is not executed */
Pls do reply ASAP.
-----Original Message-----
From: Igor Tandetnik [mailto:[EMAIL PROTECTED]
Sent: Friday, March 24, 2006 6:38 PM
To: SQLite
Subject: [sqlite] Re: Stored procedures in triggers
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
