Re: [sqlite] Trigger blocks a single transaction?

[Pavel Ivanov]

Okay, sorry, I didn't understand your initial email correctly. So the
answer to your question is yes, trigger is executed as a single
transaction and if first statement fails then other do not execute.
The only exception is when you have ON CONFLICT IGNORE.
Do you observe a different behavior? Can you illustrate it with the test script?

Pavel

On Sun, Dec 23, 2012 at 4:04 PM, Alem Biscan  wrote:
> Hi,
>
> No, i do not execute begin/commit. It is VIEW'S INSTEAD OF UPDATE TRIGGER.
> I am doing a regular update to the view from C#. Another thing is that view
> doesn't return any row affected value. Well it makes sense somehow.. It
> cannot know how many view's visible rows were affected. It lowers the
> coolnes of views and instead of trigs.
>
> Thanx
>
> On Mon, Dec 24, 2012 at 12:15 AM, Pavel Ivanov  wrote:
>
>> Do you execute all updates as one call to sqlite_exec? Or as separate
>> calls to sqlite_exec or sqlite_prepare/sqlite_step? If separate then
>> do you check return value from the calls? And do you execute
>> BEGIN/COMMIT somewhere?
>>
>> Pavel
>>
>> On Sun, Dec 23, 2012 at 2:37 PM, Alem Biscan  wrote:
>> > Hello,
>> >
>> > I am wandering if i have a block of dml statements in a trigger ( instead
>> > of ). Is it a single transaction? If something fails everything fails or?
>> >
>> > SELECT
>> > CASE
>> > WHEN
>> > NOT EXISTS ( SELECT klas_sifra FROM klasifikacija kl , tipklas tk WHERE
>> kl
>> > . tkla_id = tk . tkla_id AND kl . klas_sifra = new . grupa_klas_sifra AND
>> > tk . tkla_naziv = 'GRUPA' )
>> > THEN
>> > RAISE ( ROLLBACK , ' * Ne postoji grupa pod šifrom ' )
>> > WHEN
>> > NOT EXISTS ( SELECT klas_sifra FROM klasifikacija kl , tipklas tk WHERE
>> kl
>> > . tkla_id = tk . tkla_id AND kl . klas_sifra = new . grupa_klas_sifra AND
>> > tk . tkla_naziv = 'TIP ARTIKLA' )
>> > THEN
>> > RAISE ( ROLLBACK , ' * Ne postoji tip artikla pod šifrom ' )
>> > END;
>> >
>> > UPDATE katklas kkl
>> >SET kkl.kkl_kat_sifra  = new.kat_sifra
>> >   ,kkl.kkl_klas_sifra = new.grupa_klas_sifra
>> >  WHERE kkl.kkl_kat_sifra  = old.kat_sifra
>> >AND kkl.kkl_klas_sifra =  old.grupa_klas_sifra;
>> >
>> > UPDATE katklas kkl
>> > SET kkl.kkl_kat_sifra  = new.kat_sifra
>> >   ,kkl.kkl_klas_sifra = new.grupa_klas_sifra
>> > WHERE kkl.kkl_kat_sifra  = old.kat_sifra
>> > AND kkl.kkl_klas_sifra =  old.tip_klas_sifra;
>> >
>> > UPDATE katalog kat
>> >SET kat.kat_sifra   = new.kat_sifra
>> >   ,kat.kat_barcode = new.kat_barcode
>> >   ,kat.kat_naziv   = new.kat_naziv
>> >   ,kat.kat_jmj = new.kat_jmj
>> >   ,kat.kat_car = new.kat_car
>> >   ,kat.kat_mpc = new.kat_mpc
>> >   ,kat.kat_porez   = new.kat_porez
>> > WHERE kat.kat_sifra = old.kat_sifra;
>> >
>> > If first update fails, is it considered a failiure for the whole thing?
>> > ___
>> > sqlite-users mailing list
>> > sqlite-users@sqlite.org
>> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trigger blocks a single transaction?

[Brad Hards]

On Monday 24 December 2012 11:04:29 Alem Biscan wrote:
> Hi,
> 
> No, i do not execute begin/commit. It is VIEW'S INSTEAD OF UPDATE TRIGGER.
> I am doing a regular update to the view from C#. Another thing is that view
> doesn't return any row affected value. Well it makes sense somehow.. It
> cannot know how many view's visible rows were affected. It lowers the
> coolnes of views and instead of trigs.
You cannot DELETE, INSERT, or UPDATE a view. Views are read-only in SQLite. 
However, in many cases you can use an INSTEAD OF trigger on the view to 
accomplish the same thing. Views are removed with the DROP VIEW command.
[http://www.sqlite.org/lang_createview.html]

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

Re: [sqlite] Trigger blocks a single transaction?

[Alem Biscan]

Hi,

No, i do not execute begin/commit. It is VIEW'S INSTEAD OF UPDATE TRIGGER.
I am doing a regular update to the view from C#. Another thing is that view
doesn't return any row affected value. Well it makes sense somehow.. It
cannot know how many view's visible rows were affected. It lowers the
coolnes of views and instead of trigs.

Thanx

On Mon, Dec 24, 2012 at 12:15 AM, Pavel Ivanov  wrote:

> Do you execute all updates as one call to sqlite_exec? Or as separate
> calls to sqlite_exec or sqlite_prepare/sqlite_step? If separate then
> do you check return value from the calls? And do you execute
> BEGIN/COMMIT somewhere?
>
> Pavel
>
> On Sun, Dec 23, 2012 at 2:37 PM, Alem Biscan  wrote:
> > Hello,
> >
> > I am wandering if i have a block of dml statements in a trigger ( instead
> > of ). Is it a single transaction? If something fails everything fails or?
> >
> > SELECT
> > CASE
> > WHEN
> > NOT EXISTS ( SELECT klas_sifra FROM klasifikacija kl , tipklas tk WHERE
> kl
> > . tkla_id = tk . tkla_id AND kl . klas_sifra = new . grupa_klas_sifra AND
> > tk . tkla_naziv = 'GRUPA' )
> > THEN
> > RAISE ( ROLLBACK , ' * Ne postoji grupa pod šifrom ' )
> > WHEN
> > NOT EXISTS ( SELECT klas_sifra FROM klasifikacija kl , tipklas tk WHERE
> kl
> > . tkla_id = tk . tkla_id AND kl . klas_sifra = new . grupa_klas_sifra AND
> > tk . tkla_naziv = 'TIP ARTIKLA' )
> > THEN
> > RAISE ( ROLLBACK , ' * Ne postoji tip artikla pod šifrom ' )
> > END;
> >
> > UPDATE katklas kkl
> >SET kkl.kkl_kat_sifra  = new.kat_sifra
> >   ,kkl.kkl_klas_sifra = new.grupa_klas_sifra
> >  WHERE kkl.kkl_kat_sifra  = old.kat_sifra
> >AND kkl.kkl_klas_sifra =  old.grupa_klas_sifra;
> >
> > UPDATE katklas kkl
> > SET kkl.kkl_kat_sifra  = new.kat_sifra
> >   ,kkl.kkl_klas_sifra = new.grupa_klas_sifra
> > WHERE kkl.kkl_kat_sifra  = old.kat_sifra
> > AND kkl.kkl_klas_sifra =  old.tip_klas_sifra;
> >
> > UPDATE katalog kat
> >SET kat.kat_sifra   = new.kat_sifra
> >   ,kat.kat_barcode = new.kat_barcode
> >   ,kat.kat_naziv   = new.kat_naziv
> >   ,kat.kat_jmj = new.kat_jmj
> >   ,kat.kat_car = new.kat_car
> >   ,kat.kat_mpc = new.kat_mpc
> >   ,kat.kat_porez   = new.kat_porez
> > WHERE kat.kat_sifra = old.kat_sifra;
> >
> > If first update fails, is it considered a failiure for the whole thing?
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trigger blocks a single transaction?

[Pavel Ivanov]

Do you execute all updates as one call to sqlite_exec? Or as separate
calls to sqlite_exec or sqlite_prepare/sqlite_step? If separate then
do you check return value from the calls? And do you execute
BEGIN/COMMIT somewhere?

Pavel

On Sun, Dec 23, 2012 at 2:37 PM, Alem Biscan  wrote:
> Hello,
>
> I am wandering if i have a block of dml statements in a trigger ( instead
> of ). Is it a single transaction? If something fails everything fails or?
>
> SELECT
> CASE
> WHEN
> NOT EXISTS ( SELECT klas_sifra FROM klasifikacija kl , tipklas tk WHERE kl
> . tkla_id = tk . tkla_id AND kl . klas_sifra = new . grupa_klas_sifra AND
> tk . tkla_naziv = 'GRUPA' )
> THEN
> RAISE ( ROLLBACK , ' * Ne postoji grupa pod šifrom ' )
> WHEN
> NOT EXISTS ( SELECT klas_sifra FROM klasifikacija kl , tipklas tk WHERE kl
> . tkla_id = tk . tkla_id AND kl . klas_sifra = new . grupa_klas_sifra AND
> tk . tkla_naziv = 'TIP ARTIKLA' )
> THEN
> RAISE ( ROLLBACK , ' * Ne postoji tip artikla pod šifrom ' )
> END;
>
> UPDATE katklas kkl
>SET kkl.kkl_kat_sifra  = new.kat_sifra
>   ,kkl.kkl_klas_sifra = new.grupa_klas_sifra
>  WHERE kkl.kkl_kat_sifra  = old.kat_sifra
>AND kkl.kkl_klas_sifra =  old.grupa_klas_sifra;
>
> UPDATE katklas kkl
> SET kkl.kkl_kat_sifra  = new.kat_sifra
>   ,kkl.kkl_klas_sifra = new.grupa_klas_sifra
> WHERE kkl.kkl_kat_sifra  = old.kat_sifra
> AND kkl.kkl_klas_sifra =  old.tip_klas_sifra;
>
> UPDATE katalog kat
>SET kat.kat_sifra   = new.kat_sifra
>   ,kat.kat_barcode = new.kat_barcode
>   ,kat.kat_naziv   = new.kat_naziv
>   ,kat.kat_jmj = new.kat_jmj
>   ,kat.kat_car = new.kat_car
>   ,kat.kat_mpc = new.kat_mpc
>   ,kat.kat_porez   = new.kat_porez
> WHERE kat.kat_sifra = old.kat_sifra;
>
> If first update fails, is it considered a failiure for the whole thing?
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Trigger blocks a single transaction?

[Alem Biscan]

Hello,

I am wandering if i have a block of dml statements in a trigger ( instead
of ). Is it a single transaction? If something fails everything fails or?

SELECT
CASE
WHEN
NOT EXISTS ( SELECT klas_sifra FROM klasifikacija kl , tipklas tk WHERE kl
. tkla_id = tk . tkla_id AND kl . klas_sifra = new . grupa_klas_sifra AND
tk . tkla_naziv = 'GRUPA' )
THEN
RAISE ( ROLLBACK , ' * Ne postoji grupa pod šifrom ' )
WHEN
NOT EXISTS ( SELECT klas_sifra FROM klasifikacija kl , tipklas tk WHERE kl
. tkla_id = tk . tkla_id AND kl . klas_sifra = new . grupa_klas_sifra AND
tk . tkla_naziv = 'TIP ARTIKLA' )
THEN
RAISE ( ROLLBACK , ' * Ne postoji tip artikla pod šifrom ' )
END;

UPDATE katklas kkl
   SET kkl.kkl_kat_sifra  = new.kat_sifra
  ,kkl.kkl_klas_sifra = new.grupa_klas_sifra
 WHERE kkl.kkl_kat_sifra  = old.kat_sifra
   AND kkl.kkl_klas_sifra =  old.grupa_klas_sifra;

UPDATE katklas kkl
SET kkl.kkl_kat_sifra  = new.kat_sifra
  ,kkl.kkl_klas_sifra = new.grupa_klas_sifra
WHERE kkl.kkl_kat_sifra  = old.kat_sifra
AND kkl.kkl_klas_sifra =  old.tip_klas_sifra;

UPDATE katalog kat
   SET kat.kat_sifra   = new.kat_sifra
  ,kat.kat_barcode = new.kat_barcode
  ,kat.kat_naziv   = new.kat_naziv
  ,kat.kat_jmj = new.kat_jmj
  ,kat.kat_car = new.kat_car
  ,kat.kat_mpc = new.kat_mpc
  ,kat.kat_porez   = new.kat_porez
WHERE kat.kat_sifra = old.kat_sifra;

If first update fails, is it considered a failiure for the whole thing?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users