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 > BEGIN > DELETE FROM zWeeks; > 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. HTH. --jkl _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
