On Tue, 11 Nov 2014 17:15:53 -0600
Ben Newberg <ben.newb...@gmail.com> wrote:

> CREATE TRIGGER t_populate_zweeks
> AFTER UPDATE ON zSPs WHEN new.Procedure = 6 AND new.Flag = 1
> WITH RECURSIVE Weeks(wk) as (select 1 union all select wk + 1 from
> Weeks limit 10)
> INSERT INTO zWeeks (Week) select wk from Weeks;
> END;
> When I run this, I get an error message from sqlite3_errmsg() saying
> syntax error near "INSERT". However, when I run the DELETE, WITH and
> INSERT statements above separately without the Create Trigger DDL,
> the query runs successfully and populates my zWeeks table with values
> 1 through 10.
> Do triggers not support this behavior, or is my syntax incorrect?

You really don't want to use triggers this way.  I know you think you
do.  :-)  Everyone faces the temptation sooner or later.  But it is a
misuse of triggers, and you'll have the devil to pay in due time.  

Rather than lifting everything back up into the application, I would
suggest moving the SQL into a prepared statement inside a transaction.
It is an example of why I'd like to see SQLite support user-defined
functions (not "stored procedures") for DML purposes.  Before I did
that, though, I would make sure I actually need to persist weeks.  I'm
pretty sure weeks can be computed in a view, and it's not obvious that
performance would be worse.  

If persistence is required, consider maintaining a single offset that
can be added to weeks and incremented separately, instead incrementing
each week.  For example, you could have the first row be week 0, and
each successive row as max(week) - 1, keeping e.g. 0, -1, -2 (where
zero is oldest) and offset of 2, making 2+0, 2+-1, 2+-2, i.e. 2, 1, 0.  

Best advice is to restrict the use of triggers to enforcement of
referential integrity.  For the most part they are obsolete now that
we have DRI (e.g. declared foreign keys) support.  

Why only that?  Triggers fire regardless of application, regardless of
business rule.  They can maintain the internal consistency of the
database.  If you put business rules in triggers, you cannot change
them without affecting all applications and future uses.  Business
rules are less logical and more volatile than RI rules, too, and thus
more prone to change.  

In your case you have some incrementation of week if Procedure = 6 AND
new.Flag = 1.  The database would not be *inconsistent* if that rule
were violated; it would only be inaccurate insofar as it failed to
reflect the world it models.  A different interpretation of world and
model could be used without changing the database or requiring that
particular row relationship. 


sqlite-users mailing list

Reply via email to