My sense from these replies is that nobody bothers to try using triggers to
store their SQLite procedural code within the DB. I was skeptical when I
first learned of the technique but the trigger syntax is very
computationally permissive. Frankly, I'm still surprised by what one is
allowed to do in a trigger.
My hope is that more people will use this technique and eventually a good
proposal will emerge for syntactic sugar which condenses the syntax.
Here is a more concrete example without syntax error. Just paste into a
SQLite shell and see for yourself.
CREATE VIEW my_sproc_caller_view as SELECT (33)a, ('some_param')b, (55)c, *
from my_sproc_worktable;
CREATE TRIGGER my_sproc INSTEAD OF INSERT ON my_sproc_caller_view
BEGIN
--THE STUFF WRITTEN HERE IS STORED IN THE DB. AKA STORED PROCEDURE.
--What can be done:
-- Do something to insert/update/delete rows from the work table.
-- Using "SELECT fn(p1,p2,p3,...);" call some extension function written
in C etc.
-- Update/insert/delete other tables according to parameters a,b,c and/or
worktable rowset.
-- Call this sproc recursively up to SQLITE_MAX_TRIGGER_DEPTH
-- Have one sproc worktable per connection by using a temp worktable.
--What can't be done? Is there some operation missing here?
--One can introduce as many parameter and variable columns as needed to do
anything whatsoever.
select * from my_sproc_worktable;
END;
CREATE TABLE my_sproc_worktable(var1 TEXT,var2
TEXT,resultCol1,resultCol2,resultColN,etc);
INSERT INTO my_sproc_caller_view(a,b,c,var1) VALUES(1,2,3,4);
INSERT INTO my_sproc_caller_view(a,b,c,var2)
VALUES(7,8,9,"and_some_other_thing");
--SEE: https://sqlite.org/lang_createtrigger.html
On Wed, Apr 19, 2017 at 6:26 AM, Domingo Alvarez Duarte <mingo...@gmail.com>
wrote:
> Hello Philip !
>
> There was this attempt https://www.sqliteconcepts.org/PL_index.html and I
> tried to adapt to sqlite3 but the change on the sqlite3 vm compared to
> sqlite2 made it a lot harder.
>
> The vm of sqlite3 is not well documented and is changing all the time.
>
> But I also agreed with you if we could have "@variables" at connection
> level, query level, trigger level and also be able to write triggers in "C"
> (or another glue language), simple stored procedures (queries with
> parameters at sql level) life would be a bit easier.
>
> Cheers !
>
> On 19/04/17 08:34, Philip Warner wrote:
>
>> There is another reason to have stored procedures: encapsulating logic
>> across apps/clients.
>>
>> A great deal can be done in triggers, but not much in terms of queries or
>> complex parameterized updates.
>>
>> It would be great, imo, if triggers could have durable local storage (ie.
>> variables) and if this were built upon to allow stored procedures, life
>> would be much more fun.
>>
>> Parameterized multi-query SQL statements returning event just a single
>> row set would be fine.
>>
>>
>>
>> On 16/04/2017 2:18 AM, Richard Hipp wrote:
>>
>>> On 4/15/17, Manoj Sengottuvel <smanoj...@gmail.com> wrote:
>>>
>>>> Hi Richard,
>>>>
>>>> Is it possible to create the Stored Procedure (SP) in Sqlite?
>>>>
>>>> if not , is there any alternate way for SP?
>>>>
>>> Short answer: No.
>>>
>>> Longer answer: With SQLite, your application is the stored procedure.
>>> In a traditional client/server database like PostgreSQL or Oracle or
>>> SQL Server, every SQL statement involves a round-trip to the server.
>>> So there is a lot of latency with each command. The way applications
>>> overcome this latency is to put many queries into a stored procedure,
>>> so that only the stored procedure invocation needs to travel over the
>>> wire and latency is reduced to a single server round-trip.
>>>
>>> But with SQLite, each statement is just a procedure call. There is no
>>> network traffic, not IPC, and hence very little latency. Applications
>>> that use SQLite can be very "chatty" with the database and that is not
>>> a problem. For example, the SQLite website is backed by SQLite (duh!)
>>> and a typical page request involves 200 to 300 separate queries. That
>>> would be a performance killer with a client/server database, but with
>>> SQLite it is not a problem and the pages render in about 5
>>> milliseconds.
>>>
>>
>> _______________________________________________
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
