Re: [sqlite] Stored procedures in triggers
>Would it be useful to have recursive DELETE triggers >even without recursive INSERT or UPDATE triggers? Recursive DELETE triggers would certainly be usefull and have in fact already been asked for on this list occasionally. They would allow to move referential integrity of hierarchical data out of the application and into the database. My vote is a strong "Yes!" in favour of recursive DELETE triggers! Ralf Btw: Does the SQL standard say anything about recursive triggers? Is there a reserved word to make a trigger recursive or not? Could there be an (application defined) limit on INSERT and UPDATE recursions for to solve the endless loop / stack/memory overflow problem?
RE: [sqlite] Stored procedures in triggers
> > Thoughts? Would making recursive triggers an error rather > than just silently ignoring them break anybody's code? even if it does, it should. otherwise, people may assume that the functionality exists,and rely on it. > I'm also looking at making DELETE triggers recursive. I can > do that because recursive DELETE triggers are guaranteed to > terminate (you will eventually run out of rows to delete.) > But INSERT or UPDATE triggers might go on forever. There are > also technical issues that make recursive INSERT and UPDATE > triggers more difficult so that I would prefer to delay > implementing them. > > Comments? Would it be useful to have recursive DELETE > triggers even without recursive INSERT or UPDATE triggers? not much IMHO
Re: [sqlite] Stored procedures in triggers
"Igor Tandetnik" <[EMAIL PROTECTED]> wrote: > Cascading triggers are supported [in SQLite], 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. > I've been looking into this. Right now, if you have a recursive trigger, it just doesn't run. There is no error. I'm thinking of perhaps changing that so that you do at least get an error message. Thoughts? Would making recursive triggers an error rather than just silently ignoring them break anybody's code? I'm also looking at making DELETE triggers recursive. I can do that because recursive DELETE triggers are guaranteed to terminate (you will eventually run out of rows to delete.) But INSERT or UPDATE triggers might go on forever. There are also technical issues that make recursive INSERT and UPDATE triggers more difficult so that I would prefer to delay implementing them. Comments? Would it be useful to have recursive DELETE triggers even without recursive INSERT or UPDATE triggers? -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Stored procedures in triggers
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 >
