[sqlite] Trigger blocks a single transaction?
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
Re: [sqlite] Trigger blocks a single transaction?
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 biscana...@gmail.com 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
Re: [sqlite] Trigger blocks a single transaction?
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 paiva...@gmail.com 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 biscana...@gmail.com 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?
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?
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 biscana...@gmail.com 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 paiva...@gmail.com 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 biscana...@gmail.com 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
