On Thu Oct 09, 2014 at 11:29:49AM -0700, J Decker wrote:
> they can be implemented through registered extensions..(well no probably
> not how you're thinking)..
>
> but apparently can't add syntax like 'EXEC" ... but could make them be
> like "select * from (stored_proc)" as an alias for "exec (stored proc)"
I use BEFORE INSERT triggers that end with a SELECT RAISE(IGNORE). That
way I can use the following syntax to perform multiple operations
within a single statement:
INSERT INTO
my_function_name(
arg1,
arg2,
arg3
)
VALUES (
val1,
val2,
val3
)
Obviously you can't get a result from that, but it would of course be
possible to let the insert succeed or to insert a "result row"
somewhere. Unfortunately CTEs don't work inside triggers so complicated
logic is somewhat limited and/or must be spread across multiple
triggers.
> For lack of portability I haven't used them; what's a good use case
> for stored procedures?
I generally use them for storing entities that must be entered in many
tables, where it nicely presents an API to the caller that closely maps
to the single action they want to take. This provides:
Simplicity: a single statement can replace many individual
statements, putting more of your application inside the database
and can often remove the need for an explicit transaction.
Efficiency: for non-C languages there is less translation between
the language/SQLite boundary.
--
Mark Lawrence
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
