On Wed Apr 19, 2017 at 09:53:07AM -0700, petern wrote:
> 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.
Just to provide at least one data point in the other direction, I use
triggers quite heavily as a kind of stored procedure.
Instead of basing them on views however I use real tables and AFTER
INSERT triggers whose final statement deletes the NEW row just
inserted.
I see two benefits to the use of AFTER INSERT triggers:
* Constraints are enforced so SQLite catches invalid
"procedure calls."
* Default values for columns (or "arguments") can be defined. This
is very useful if you want to use the incoming value in multiple
statements - you don't have to hardcode a bunch of
COALESCE(NEW.col, $DEFAULT) values everywhere.
Because the INSERT/TRIGGER/DELETE happens within a transaction I expect
the data never to hit the disk. I haven't measured it but I guess the
performance would not be too far off the INSTEAD-OF/VIEW trigger.
> CREATE VIEW my_sproc_caller_view as SELECT (33)a, ('some_param')b,
> (55)c, * from my_sproc_worktable;
My own naming convention uses tables like "func_action_name".
--
Mark Lawrence
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
