Re: [sqlite] Triggers and CTE's

2014-11-12 Thread James K. Lowden
On Tue, 11 Nov 2014 17:15:53 -0600 Ben Newberg 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)

Re: [sqlite] Triggers and CTE's

2014-11-11 Thread Richard Hipp
On Tue, Nov 11, 2014 at 9:01 PM, Igor Tandetnik wrote: > On 11/11/2014 8:37 PM, Richard Hipp wrote: > >> On Tue, Nov 11, 2014 at 8:22 PM, Igor Tandetnik >> wrote: >> >> On 11/11/2014 6:15 PM, Ben Newberg wrote: >> >>> >>> Looks like a bug to me. The statement works standalone, but not within a >

Re: [sqlite] Triggers and CTE's

2014-11-11 Thread Igor Tandetnik
On 11/11/2014 8:37 PM, Richard Hipp wrote: On Tue, Nov 11, 2014 at 8:22 PM, Igor Tandetnik wrote: On 11/11/2014 6:15 PM, Ben Newberg wrote: Looks like a bug to me. The statement works standalone, but not within a trigger. There are many limitations and restrictions on the statements inside

Re: [sqlite] Triggers and CTE's

2014-11-11 Thread Ben Newberg
Thanks everyone. I will have the programming language do the work on this one instead of going the trigger route. On Nov 11, 2014 7:39 PM, "Richard Hipp" wrote: > On Tue, Nov 11, 2014 at 8:22 PM, Igor Tandetnik > wrote: > > On 11/11/2014 6:15 PM, Ben Newberg wrote: > > > > Looks like a bug to me

Re: [sqlite] Triggers and CTE's

2014-11-11 Thread Richard Hipp
On Tue, Nov 11, 2014 at 8:22 PM, Igor Tandetnik wrote: On 11/11/2014 6:15 PM, Ben Newberg wrote: > > Looks like a bug to me. The statement works standalone, but not within a > trigger. > There are many limitations and restrictions on the statements inside of triggers. See the trigger documentat

Re: [sqlite] Triggers and CTE's

2014-11-11 Thread Simon Slavin
On 11 Nov 2014, at 11:59pm, Ben Newberg wrote: > The 10 is just an arbitrary value I chose for this example. The user > actually determines the value at run-time, so this value could be any > integer. I have a way to settle that, if only I could figure out how I can > get this trigger working.

Re: [sqlite] Triggers and CTE's

2014-11-11 Thread Igor Tandetnik
On 11/11/2014 6:15 PM, Ben Newberg 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;

Re: [sqlite] Triggers and CTE's

2014-11-11 Thread Ben Newberg
Thanks Simon. The 10 is just an arbitrary value I chose for this example. The user actually determines the value at run-time, so this value could be any integer. I have a way to settle that, if only I could figure out how I can get this trigger working. BEN On Tue, Nov 11, 2014 at 5:41 PM, Simon

Re: [sqlite] Triggers and CTE's

2014-11-11 Thread Simon Slavin
On 11 Nov 2014, at 11:15pm, Ben Newberg wrote: > WITH RECURSIVE Weeks(wk) as (select 1 union all select wk + 1 from Weeks > limit 10) > INSERT INTO zWeeks (Week) select wk from Weeks; Just use 10 INSERT commands. I don't know what's causing your error message, but your code will be simpler if

[sqlite] Triggers and CTE's

2014-11-11 Thread Ben Newberg
All, Is it possible to have CTE's within triggers? The way I read the 'SQL As Understood By SQLite', one can, but I could be misinterpreting it. My DDL for my trigger is as follows: CREATE TRIGGER t_populate_zweeks AFTER UPDATE ON zSPs WHEN new.Procedure = 6 AND new.Flag = 1 BEGIN DELETE FROM zW