Re: [sqlite] playing with triggers

[Simon Slavin]

On 19 Aug 2010, at 11:22pm, Igor Tandetnik wrote:

> No, cascading triggers (one trigger causing another to fire) have "always" 
> worked ("always" meaning long enough that I can't recall when this was *not* 
> the case). Recursive triggers (a trigger causing itself to fire, directly or 
> indirectly) are relatively new (a couple years old) and have to be explicitly 
> enabled, for backward compatibilty with existing schemas.

Thanks.

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

Re: [sqlite] playing with triggers

[Igor Tandetnik]

Simon Slavin  wrote:
> If that worries you then you should be aware that the same problem applies 
> when one TRIGGER triggers another:
> 
> http://www.sqlite.org/pragma.html#pragma_recursive_triggers
> 
> You have to remember to turn it on in your application.  My understanding of 
> the term 'recursive triggers' is that it refers not
> just to when a trigger triggers itself (what 'recursive' means to me), but 
> also when any trigger triggers another. However I may
> be wrong about this.  

No, cascading triggers (one trigger causing another to fire) have "always" 
worked ("always" meaning long enough that I can't recall when this was *not* 
the case). Recursive triggers (a trigger causing itself to fire, directly or 
indirectly) are relatively new (a couple years old) and have to be explicitly 
enabled, for backward compatibilty with existing schemas.
-- 
Igor Tandetnik


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

Re: [sqlite] playing with triggers

[Simon Slavin]

On 19 Aug 2010, at 9:00pm, David Bicking wrote:

> I haven't tried RAISE(ROLLBACK... as that seems to severe. 
> RAISE(ABORT... removes the initial insert to Table1, which I want to avoid.
> RAISE(FAIL.. on lets say the fourth record inserted in to Table2, would leave 
> the first three there, which I can't let happen. It is all or nothing for 
> data changes below Table1.
> 
> Which leads me to believe I can't do what I want without application code 
> supervising the changes.

With requirements that complicated you either need to work each TRIGGER 
differently or, as you say, implement the logic in your application.

>> with special regard to those two constructions and see
>> 
>> http://www.sqlite.org/cvstrac/wiki?p=ForeignKeyTriggers
> 
> I do have foreign keys declared. Mind you in my tests they don't work if I 
> forget to issue Pragma foreign_keys=on. Since I am a forgetful person, it 
> seems like I really can't trust foreign keys and it is best to never enable 
> them. 

If that worries you then you should be aware that the same problem applies when 
one TRIGGER triggers another:

http://www.sqlite.org/pragma.html#pragma_recursive_triggers

You have to remember to turn it on in your application.  My understanding of 
the term 'recursive triggers' is that it refers not just to when a trigger 
triggers itself (what 'recursive' means to me), but also when any trigger 
triggers another. However I may be wrong about this.

> Or is there a way to force them to be enabled at all times? (i.e. not trust 
> me to remember to have any and all applications that talk to the data file to 
> remember to issue the pragma statement.)

http://www.sqlite.org/pragma.html#pragma_foreign_keys

says in part

"As of SQLite version 3.6.19, the default setting for foreign key enforcement 
is OFF. However, that might change in a future release of SQLite. To minimize 
future problems, applications should set the foreign key enforcement flag as 
required by the application and not depend on the default setting."

So the answer is not yet, but maybe in a future release.  However, there's a 
similar note about triggers !

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

Re: [sqlite] playing with triggers

[Doug Currie]

On Aug 19, 2010, at 4:00 PM, David Bicking wrote:

> I haven't tried RAISE(ROLLBACK... as that seems to severe. 
> RAISE(ABORT... removes the initial insert to Table1, which I want to avoid.
> RAISE(FAIL.. on lets say the fourth record inserted in to Table2, would leave 
> the first three there, which I can't let happen. It is all or nothing for 
> data changes below Table1.
> 
> Which leads me to believe I can't do what I want without application code 
> supervising the changes.

Would SAVEPOINTs help you here? http://www.sqlite.org/lang_savepoint.html

I've never tried using ROLLBACK TO in a trigger.

e

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

Re: [sqlite] playing with triggers

[David Bicking]

--- On Thu, 8/19/10, Simon Slavin  wrote:

> 
> On 19 Aug 2010, at 8:10pm, David Bicking wrote:
> 
> > The way it is set up, if any of the updates/inserts
> done by the triggers fail, everything rolls back, including
> the original data that caused the triggers. What I want to
> happen is that while everything else gets rolled back,
> Table1 still has its data, along with the error messages
> returned by the triggers.
> > 
> > Nearest I can tell you can't do that with triggers,
> but I really don't understand how they work, so maybe I am
> wrong.
> 
> You may be able to do this with ON CONFLICT and
> RAISE.  See
> 
> http://www.sqlite.org/lang_createtrigger.html
> 

I did read that, and think I mostly understand it, With it, and help from this 
list, I have gotten as far as I have. 

I haven't tried RAISE(ROLLBACK... as that seems to severe. 
RAISE(ABORT... removes the initial insert to Table1, which I want to avoid.
RAISE(FAIL.. on lets say the fourth record inserted in to Table2, would leave 
the first three there, which I can't let happen. It is all or nothing for data 
changes below Table1.

Which leads me to believe I can't do what I want without application code 
supervising the changes.

> with special regard to those two constructions and see
> 
> http://www.sqlite.org/cvstrac/wiki?p=ForeignKeyTriggers
> 

I do have foreign keys declared. Mind you in my tests they don't work if I 
forget to issue Pragma foreign_keys=on. Since I am a forgetful person, it seems 
like I really can't trust foreign keys and it is best to never enable them. 

Or is there a way to force them to be enabled at all times? (i.e. not trust me 
to remember to have any and all applications that talk to the data file to 
remember to issue the pragma statement.)

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

Re: [sqlite] playing with triggers

[Simon Slavin]

On 19 Aug 2010, at 8:10pm, David Bicking wrote:

> The way it is set up, if any of the updates/inserts done by the triggers 
> fail, everything rolls back, including the original data that caused the 
> triggers. What I want to happen is that while everything else gets rolled 
> back, Table1 still has its data, along with the error messages returned by 
> the triggers.
> 
> Nearest I can tell you can't do that with triggers, but I really don't 
> understand how they work, so maybe I am wrong.

You may be able to do this with ON CONFLICT and RAISE.  See

http://www.sqlite.org/lang_createtrigger.html

with special regard to those two constructions and see

http://www.sqlite.org/cvstrac/wiki?p=ForeignKeyTriggers

for some examples.  However, a lot of work that's done with triggers is really 
about foreign keys, and SQLite now implements foreign keys so you don't have to 
simulate them in such a bulky way.  Read

http://www.hwaci.com/sw/sqlite/foreignkeys.html

before you get too into triggers.

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

[sqlite] playing with triggers

[David Bicking]

I am more or less playing with triggers trying to learn what they can do.

I have a setup where I write data to Table1. An after insert trigger looks up 
the newly written data codes in Table1, and writes 1 to 4 records to Table2. An 
after insert trigger on Table2 looks at the new data and updates a handful of 
other table, creating records if need be.

It all works. It is kind of neat to insert one record, and see changes across a 
half dozen other tables.

But... I want it to do more. 

The way it is set up, if any of the updates/inserts done by the triggers fail, 
everything rolls back, including the original data that caused the triggers. 
What I want to happen is that while everything else gets rolled back, Table1 
still has its data, along with the error messages returned by the triggers.

Nearest I can tell you can't do that with triggers, but I really don't 
understand how they work, so maybe I am wrong.

So, is there a way to do the following:

CREATE TRIGGER table1_insert AFTER INSERT ON TABLE1
BEGIN
   -- write to other tables (which can fire triggers or return
   --   constraint failures)
   -- if writes were not successful (constraint failures, etc.)
   --update table1 with message in Errors column
   -- else
   --write 'ok' to Errors column.
END;

Thanks,
David


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