Re: [sqlite] Trouble with TRIGGERS

[Simon Slavin]

On 16 Nov 2010, at 10:18pm, Bernard Ertl wrote:

> Kees Nuyt wrote:
>> 
> 
>>> Nicolas Williams-2 wrote:
 Do you have recursive triggers enabled?
>>> 
>>> I'm not sure.  How do I check?
>> 
>> http://www.sqlite.org/pragma.html#pragma_recursive_triggers
> 
> I'm not using any pragma commands, so no, I'm not using recursive triggers.

In that case, although you can define a trigger that would trigger itself, 
SQLite will silently drop all such situations: the trigger will be fired once, 
but attempts to fire itself will be ignored.  You can use that PRAGMA to change 
this behaviour.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trouble with TRIGGERS

[Bernard Ertl]

Kees Nuyt wrote:
> 
>> Nicolas Williams-2 wrote:
>> > Do you have recursive triggers enabled?
>>
>> I'm not sure.  How do I check?
> 
> http://www.sqlite.org/pragma.html#pragma_recursive_triggers
> 

I'm not using any pragma commands, so no, I'm not using recursive triggers.

-- 
View this message in context: 
http://old.nabble.com/Trouble-with-TRIGGERS-tp30228089p30233595.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trouble with TRIGGERS

[Kees Nuyt]

On Tue, 16 Nov 2010 14:07:34 -0800 (PST), Bernard Ertl
 wrote:

>
>
> Nicolas Williams-2 wrote:
> > Do you have recursive triggers enabled?
>
> I'm not sure.  How do I check?

http://www.sqlite.org/pragma.html#pragma_recursive_triggers
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trouble with TRIGGERS

[Bernard Ertl]

Nicolas Williams-2 wrote:
> Do you have recursive triggers enabled?

I'm not sure.  How do I check?
-- 
View this message in context: 
http://old.nabble.com/Trouble-with-TRIGGERS-tp30228089p30233496.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trouble with TRIGGERS

[Nicolas Williams]

Do you have recursive triggers enabled?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trouble with TRIGGERS

[Bernard Ertl]

Dan Kennedy-4 wrote:
> 
> Sounds like it.
> 
> Calling sqlite3_prepare_v2() generates the VM code for all
> triggers that could possibly be invoked by your statement.
> All it considers when determining which triggers might be
> needed is the type of statement (UPDATE, DELETE, INSERT) and
> for UPDATES, the columns updated.
> 

Thanks Dan.  However, I think it's more correct to say that it generates the
VM code for all triggers that could possibly be invoked by the statement
*and any related triggers*.

It appears to expand the pool of possible triggers on the fly based upon the
content of each trigger that it's queuing up.  For example, this trigger:

CREATE TRIGGER fki_Tasks_PerComp_Range AFTER INSERT ON Tasks FOR EACH ROW
WHEN NEW.PerComp IS NULL
   BEGIN
   UPDATE Tasks SET PerComp = 0 WHERE RowID = NEW.RowID;
   END;

Is causing all my triggers related to an update on the Tasks table to be
queued up when I execute an INSERT on Tasks *with* PerComp = 0 (not null).

I guess I was expecting a short circuit evaluation on the FOR EACH ROW WHEN
conditions.
-- 
View this message in context: 
http://old.nabble.com/Trouble-with-TRIGGERS-tp30228089p30232856.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trouble with TRIGGERS

[Dan Kennedy]

On 11/16/2010 06:25 AM, Bernard Ertl wrote:
> Hi,
>
> I'm experiencing some performance issues with triggers at the moment and 
> hoping someone can help shed some light on what is happening.
>
> I have a database with ~20 tables and>100 triggers.  I noticed a severe 
> performance degradation after adding the last few triggers and it puzzled me 
> because the triggers I added should not be executed by the statements I was 
> testing.
>
> In a nutshell, I am inserting a record into a table.  The last few triggers 
> causing the severe performance degradation should only be triggered if there 
> is an update to the table.
>
> I tried capturing the output from the EXPLAIN statement and, as best as I can 
> tell, SQLite is queing up triggers if there is a possibility that they will 
> be needed (but before an evaluation confirms it).
>
> For example, I have a trigger that, upon an insert, tests a condition and 
> possibly performs an update pending the results of the condition.  As a 
> result, a whole slew of triggers conditioned to an update on the table are 
> showing up in the EXPLAIN output (immediately after the insert trigger) even 
> though the result of the initial condition is false and the update is not 
> executed.  From what I'm seeing there is a huge cascade of triggers listed in 
> the EXPLAIN output that should never be executed because the conditions for 
> executing them are never met.
>
> Can anyone confirm how SQLite processes triggers?  Am I interpreting the 
> EXPLAIN results correctly?

Sounds like it.

Calling sqlite3_prepare_v2() generates the VM code for all
triggers that could possibly be invoked by your statement.
All it considers when determining which triggers might be
needed is the type of statement (UPDATE, DELETE, INSERT) and
for UPDATES, the columns updated.

Dan.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Trouble with TRIGGERS

[Bernard Ertl]

Hi,

I'm experiencing some performance issues with triggers at the moment and hoping 
someone can help shed some light on what is happening.

I have a database with ~20 tables and >100 triggers.  I noticed a severe 
performance degradation after adding the last few triggers and it puzzled me 
because the triggers I added should not be executed by the statements I was 
testing.

In a nutshell, I am inserting a record into a table.  The last few triggers 
causing the severe performance degradation should only be triggered if there is 
an update to the table.

I tried capturing the output from the EXPLAIN statement and, as best as I can 
tell, SQLite is queing up triggers if there is a possibility that they will be 
needed (but before an evaluation confirms it).

For example, I have a trigger that, upon an insert, tests a condition and 
possibly performs an update pending the results of the condition.  As a result, 
a whole slew of triggers conditioned to an update on the table are showing up 
in the EXPLAIN output (immediately after the insert trigger) even though the 
result of the initial condition is false and the update is not executed.  From 
what I'm seeing there is a huge cascade of triggers listed in the EXPLAIN 
output that should never be executed because the conditions for executing them 
are never met.

Can anyone confirm how SQLite processes triggers?  Am I interpreting the 
EXPLAIN results correctly?

Cordially,
Bernard




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] trouble with TRIGGERs

[P Kishor]

I have a table

CREATE TABLE foo (
  foo_id INTEGER PRIMARY KEY,
  foo_name TEXT,
  foo_text TEXT
);

I have created a virtual table to do full text search

CREATE VIRTUAL TABLE fts_foo (
USING fts2(foo_text);

I have populated this table

INSERT INTO fts_foo (rowid, foo_text)
VALUES (foo.foo_id, foo.foo_text)

for every row in foo. So far so good. Now, I want to create TRIGGERs
that update fts_foo every time foo.foo_text is updated or a new record
is inserted into foo.

CREATE TRIGGER update_fts
AFTER UPDATE OF foo_text ON foo
  BEGIN
UPDATE fts_foo
SET foo_text = new.foo_text
WHERE rowid = old.foo_id
  END;

CREATE TRIGGER insert_fts
AFTER INSERT ON foo
  BEGIN
INSERT INTO fts_foo (rowid, foo_text)
VALUES (new.foo_id, new.foo_text)
  END;

But I am getting a syntax error in both cases. What am I doing wrong?

-
To unsubscribe, send email to [EMAIL PROTECTED]
-