From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id D45F1290C5 for ; Thu, 15 Mar 2018 14:22:15 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 7LYnEK-F3fCW for ; Thu, 15 Mar 2018 14:22:15 -0400 (EDT) Received: from smtp55.i.mail.ru (smtp55.i.mail.ru [217.69.128.35]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTPS id E626A29069 for ; Thu, 15 Mar 2018 14:22:14 -0400 (EDT) From: Kirill Yukhin Subject: [tarantool-patches] [PATCH 2/2] sql: block trigger creation inside a transaction Date: Thu, 15 Mar 2018 21:21:40 +0300 Message-Id: <789969f10fb2bc6c25bdf97aa69bcb8b5432b0a0.1521137308.git.kyukhin@tarantool.org> In-Reply-To: References: In-Reply-To: References: Sender: tarantool-patches-bounce@freelists.org Errors-to: tarantool-patches-bounce@freelists.org Reply-To: tarantool-patches@freelists.org List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: tarantool-patches List-subscribe: List-owner: List-post: List-archive: To: tarantool-patches@freelists.org Cc: Kirill Yukhin Since DDL is prohibited inside a transaction, this patch prohibits trigger creation and drop inside a transaction. This was done by adding dummy trigger on replace into _trigger space. This trigger only check if this not multistatement transaction. Also, make assert that schema cokie wasn't changed during transaction rollback if any. Testsuite updated as well. Closes #3239 --- src/box/alter.cc | 15 +++++ src/box/alter.h | 1 + src/box/schema.cc | 2 +- src/box/sql/build.c | 9 ++- src/box/sql/main.c | 17 +---- src/box/sql/parse.c | 147 +++++++++++++++++++++-------------------- src/box/sql/parse.y | 2 + src/box/sql/vdbe.c | 1 - test/sql-tap/trigger1.test.lua | 25 ++++++- test/sql-tap/trigger9.test.lua | 77 +++++++++++---------- 10 files changed, 171 insertions(+), 125 deletions(-) diff --git a/src/box/alter.cc b/src/box/alter.cc index 77b2342e2..9f9a08d1f 100644 --- a/src/box/alter.cc +++ b/src/box/alter.cc @@ -3057,6 +3057,17 @@ lock_before_dd(struct trigger *trigger, void *event) txn_on_rollback(txn, on_rollback); } +/** + * A trigger invoked on replace in a space containing + * SQL triggers. + */ +static void +on_replace_dd_trigger(struct trigger * /* trigger */, void *event) +{ + struct txn *txn = (struct txn *) event; + txn_check_singlestatement_xc(txn, "Space _trigger"); +} + struct trigger alter_space_on_replace_space = { RLIST_LINK_INITIALIZER, on_replace_dd_space, NULL, NULL }; @@ -3117,4 +3128,8 @@ struct trigger on_stmt_begin_truncate = { RLIST_LINK_INITIALIZER, lock_before_dd, NULL, NULL }; +struct trigger on_replace_trigger = { + RLIST_LINK_INITIALIZER, on_replace_dd_trigger, NULL, NULL +}; + /* vim: set foldmethod=marker */ diff --git a/src/box/alter.h b/src/box/alter.h index fb5f65a68..8ea29c77b 100644 --- a/src/box/alter.h +++ b/src/box/alter.h @@ -44,6 +44,7 @@ extern struct trigger on_replace_cluster; extern struct trigger on_replace_sequence; extern struct trigger on_replace_sequence_data; extern struct trigger on_replace_space_sequence; +extern struct trigger on_replace_trigger; extern struct trigger on_stmt_begin_space; extern struct trigger on_stmt_begin_index; extern struct trigger on_stmt_begin_truncate; diff --git a/src/box/schema.cc b/src/box/schema.cc index 038f8ce2b..8f25161ac 100644 --- a/src/box/schema.cc +++ b/src/box/schema.cc @@ -330,7 +330,7 @@ schema_init() /* _trigger - all existing SQL triggers. */ key_def_set_part(key_def, 0 /* part no */, 0 /* field no */, FIELD_TYPE_STRING, ON_CONFLICT_ACTION_ABORT, NULL); - sc_space_new(BOX_TRIGGER_ID, "_trigger", key_def, NULL, NULL); + sc_space_new(BOX_TRIGGER_ID, "_trigger", key_def, &on_replace_trigger, NULL); free(key_def); key_def = key_def_new(2); /* part count */ diff --git a/src/box/sql/build.c b/src/box/sql/build.c index a719136cd..13e5384bd 100644 --- a/src/box/sql/build.c +++ b/src/box/sql/build.c @@ -2364,7 +2364,14 @@ sqlite3CodeDropTable(Parse * pParse, Table * pTab, int isView) */ sqlite3VdbeAddOp4(v, OP_DropTable, 0, 0, 0, pTab->zName, 0); - sqlite3ChangeCookie(pParse); + + /* FIXME: DDL is impossible inside a transaction so far. + * Replace to _space/_index will fail if active + * transaction. So, don't pretend, that we are able to + * anything back. To be fixed when transactions + * DDL are enabled. + */ + /* sqlite3ChangeCookie(pParse); */ sqliteViewResetAll(db); } diff --git a/src/box/sql/main.c b/src/box/sql/main.c index 5a0f372b3..e0aa61f40 100644 --- a/src/box/sql/main.c +++ b/src/box/sql/main.c @@ -977,20 +977,9 @@ sqlite3RollbackAll(Vdbe * pVdbe, int tripCode) struct session *user_session = current_session(); assert(sqlite3_mutex_held(db->mutex)); - if ((user_session->sql_flags & SQLITE_InternChanges) != 0 - && db->init.busy == 0) { - sqlite3ExpirePreparedStatements(db); - sqlite3ResetAllSchemasOfConnection(db); - - db->init.busy = 1; - db->pSchema = sqlite3SchemaCreate(db); - int rc = sqlite3InitDatabase(db); - if (rc != SQLITE_OK) - sqlite3SchemaClear(db); - db->init.busy = 0; - if (rc == SQLITE_OK) - sqlite3CommitInternalChanges(); - } + /* DDL is impossible inside a transaction. */ + assert((user_session->sql_flags & SQLITE_InternChanges) == 0 + || db->init.busy == 1); /* Any deferred constraint violations have now been resolved. */ pVdbe->nDeferredCons = 0; diff --git a/src/box/sql/parse.c b/src/box/sql/parse.c index 0019b77dc..f938a9a3c 100644 --- a/src/box/sql/parse.c +++ b/src/box/sql/parse.c @@ -1512,7 +1512,7 @@ sqlite3SrcListDelete(pParse->db, (yypminor->yy387)); case 184: /* with */ case 229: /* wqlist */ { -#line 1510 "parse.y" +#line 1511 "parse.y" sqlite3WithDelete(pParse->db, (yypminor->yy151)); #line 1518 "parse.c" } @@ -1541,14 +1541,14 @@ sqlite3IdListDelete(pParse->db, (yypminor->yy40)); case 221: /* trigger_cmd_list */ case 226: /* trigger_cmd */ { -#line 1384 "parse.y" +#line 1385 "parse.y" sqlite3DeleteTriggerStep(pParse->db, (yypminor->yy427)); #line 1547 "parse.c" } break; case 223: /* trigger_event */ { -#line 1370 "parse.y" +#line 1371 "parse.y" sqlite3IdListDelete(pParse->db, (yypminor->yy10).b); #line 1554 "parse.c" } @@ -3498,126 +3498,127 @@ static void yy_reduce( Token all; all.z = yymsp[-3].minor.yy0.z; all.n = (int)(yymsp[0].minor.yy0.z - yymsp[-3].minor.yy0.z) + yymsp[0].minor.yy0.n; + pParse->initiateTTrans = false; sqlite3FinishTrigger(pParse, yymsp[-1].minor.yy427, &all); } -#line 3504 "parse.c" +#line 3505 "parse.c" break; case 221: /* trigger_decl ::= TRIGGER ifnotexists nm trigger_time trigger_event ON fullname foreach_clause when_clause */ -#line 1358 "parse.y" +#line 1359 "parse.y" { sqlite3BeginTrigger(pParse, &yymsp[-6].minor.yy0, yymsp[-5].minor.yy52, yymsp[-4].minor.yy10.a, yymsp[-4].minor.yy10.b, yymsp[-2].minor.yy387, yymsp[0].minor.yy362, yymsp[-7].minor.yy52); yymsp[-8].minor.yy0 = yymsp[-6].minor.yy0; /*yymsp[-8].minor.yy0-overwrites-T*/ } -#line 3512 "parse.c" +#line 3513 "parse.c" break; case 222: /* trigger_time ::= BEFORE */ -#line 1364 "parse.y" +#line 1365 "parse.y" { yymsp[0].minor.yy52 = TK_BEFORE; } -#line 3517 "parse.c" +#line 3518 "parse.c" break; case 223: /* trigger_time ::= AFTER */ -#line 1365 "parse.y" +#line 1366 "parse.y" { yymsp[0].minor.yy52 = TK_AFTER; } -#line 3522 "parse.c" +#line 3523 "parse.c" break; case 224: /* trigger_time ::= INSTEAD OF */ -#line 1366 "parse.y" +#line 1367 "parse.y" { yymsp[-1].minor.yy52 = TK_INSTEAD;} -#line 3527 "parse.c" +#line 3528 "parse.c" break; case 225: /* trigger_time ::= */ -#line 1367 "parse.y" +#line 1368 "parse.y" { yymsp[1].minor.yy52 = TK_BEFORE; } -#line 3532 "parse.c" +#line 3533 "parse.c" break; case 226: /* trigger_event ::= DELETE|INSERT */ case 227: /* trigger_event ::= UPDATE */ yytestcase(yyruleno==227); -#line 1371 "parse.y" +#line 1372 "parse.y" {yymsp[0].minor.yy10.a = yymsp[0].major; /*A-overwrites-X*/ yymsp[0].minor.yy10.b = 0;} -#line 3538 "parse.c" +#line 3539 "parse.c" break; case 228: /* trigger_event ::= UPDATE OF idlist */ -#line 1373 "parse.y" +#line 1374 "parse.y" {yymsp[-2].minor.yy10.a = TK_UPDATE; yymsp[-2].minor.yy10.b = yymsp[0].minor.yy40;} -#line 3543 "parse.c" +#line 3544 "parse.c" break; case 229: /* when_clause ::= */ -#line 1380 "parse.y" +#line 1381 "parse.y" { yymsp[1].minor.yy362 = 0; } -#line 3548 "parse.c" +#line 3549 "parse.c" break; case 230: /* when_clause ::= WHEN expr */ -#line 1381 "parse.y" +#line 1382 "parse.y" { yymsp[-1].minor.yy362 = yymsp[0].minor.yy162.pExpr; } -#line 3553 "parse.c" +#line 3554 "parse.c" break; case 231: /* trigger_cmd_list ::= trigger_cmd_list trigger_cmd SEMI */ -#line 1385 "parse.y" +#line 1386 "parse.y" { assert( yymsp[-2].minor.yy427!=0 ); yymsp[-2].minor.yy427->pLast->pNext = yymsp[-1].minor.yy427; yymsp[-2].minor.yy427->pLast = yymsp[-1].minor.yy427; } -#line 3562 "parse.c" +#line 3563 "parse.c" break; case 232: /* trigger_cmd_list ::= trigger_cmd SEMI */ -#line 1390 "parse.y" +#line 1391 "parse.y" { assert( yymsp[-1].minor.yy427!=0 ); yymsp[-1].minor.yy427->pLast = yymsp[-1].minor.yy427; } -#line 3570 "parse.c" +#line 3571 "parse.c" break; case 233: /* trnm ::= nm DOT nm */ -#line 1401 "parse.y" +#line 1402 "parse.y" { yymsp[-2].minor.yy0 = yymsp[0].minor.yy0; sqlite3ErrorMsg(pParse, "qualified table names are not allowed on INSERT, UPDATE, and DELETE " "statements within triggers"); } -#line 3580 "parse.c" +#line 3581 "parse.c" break; case 234: /* tridxby ::= INDEXED BY nm */ -#line 1413 "parse.y" +#line 1414 "parse.y" { sqlite3ErrorMsg(pParse, "the INDEXED BY clause is not allowed on UPDATE or DELETE statements " "within triggers"); } -#line 3589 "parse.c" +#line 3590 "parse.c" break; case 235: /* tridxby ::= NOT INDEXED */ -#line 1418 "parse.y" +#line 1419 "parse.y" { sqlite3ErrorMsg(pParse, "the NOT INDEXED clause is not allowed on UPDATE or DELETE statements " "within triggers"); } -#line 3598 "parse.c" +#line 3599 "parse.c" break; case 236: /* trigger_cmd ::= UPDATE orconf trnm tridxby SET setlist where_opt */ -#line 1431 "parse.y" +#line 1432 "parse.y" {yymsp[-6].minor.yy427 = sqlite3TriggerUpdateStep(pParse->db, &yymsp[-4].minor.yy0, yymsp[-1].minor.yy382, yymsp[0].minor.yy362, yymsp[-5].minor.yy52);} -#line 3603 "parse.c" +#line 3604 "parse.c" break; case 237: /* trigger_cmd ::= insert_cmd INTO trnm idlist_opt select */ -#line 1435 "parse.y" +#line 1436 "parse.y" {yymsp[-4].minor.yy427 = sqlite3TriggerInsertStep(pParse->db, &yymsp[-2].minor.yy0, yymsp[-1].minor.yy40, yymsp[0].minor.yy279, yymsp[-4].minor.yy52);/*A-overwrites-R*/} -#line 3608 "parse.c" +#line 3609 "parse.c" break; case 238: /* trigger_cmd ::= DELETE FROM trnm tridxby where_opt */ -#line 1439 "parse.y" +#line 1440 "parse.y" {yymsp[-4].minor.yy427 = sqlite3TriggerDeleteStep(pParse->db, &yymsp[-2].minor.yy0, yymsp[0].minor.yy362);} -#line 3613 "parse.c" +#line 3614 "parse.c" break; case 239: /* trigger_cmd ::= select */ -#line 1443 "parse.y" +#line 1444 "parse.y" {yymsp[0].minor.yy427 = sqlite3TriggerSelectStep(pParse->db, yymsp[0].minor.yy279); /*A-overwrites-X*/} -#line 3618 "parse.c" +#line 3619 "parse.c" break; case 240: /* expr ::= RAISE LP IGNORE RP */ -#line 1446 "parse.y" +#line 1447 "parse.y" { spanSet(&yymsp[-3].minor.yy162,&yymsp[-3].minor.yy0,&yymsp[0].minor.yy0); /*A-overwrites-X*/ yymsp[-3].minor.yy162.pExpr = sqlite3PExpr(pParse, TK_RAISE, 0, 0); @@ -3625,10 +3626,10 @@ static void yy_reduce( yymsp[-3].minor.yy162.pExpr->affinity = ON_CONFLICT_ACTION_IGNORE; } } -#line 3629 "parse.c" +#line 3630 "parse.c" break; case 241: /* expr ::= RAISE LP raisetype COMMA STRING RP */ -#line 1453 "parse.y" +#line 1454 "parse.y" { spanSet(&yymsp[-5].minor.yy162,&yymsp[-5].minor.yy0,&yymsp[0].minor.yy0); /*A-overwrites-X*/ yymsp[-5].minor.yy162.pExpr = sqlite3ExprAlloc(pParse->db, TK_RAISE, &yymsp[-1].minor.yy0, 1); @@ -3636,85 +3637,85 @@ static void yy_reduce( yymsp[-5].minor.yy162.pExpr->affinity = (char)yymsp[-3].minor.yy52; } } -#line 3640 "parse.c" +#line 3641 "parse.c" break; case 242: /* raisetype ::= ROLLBACK */ -#line 1463 "parse.y" +#line 1464 "parse.y" {yymsp[0].minor.yy52 = ON_CONFLICT_ACTION_ROLLBACK;} -#line 3645 "parse.c" +#line 3646 "parse.c" break; case 244: /* raisetype ::= FAIL */ -#line 1465 "parse.y" +#line 1466 "parse.y" {yymsp[0].minor.yy52 = ON_CONFLICT_ACTION_FAIL;} -#line 3650 "parse.c" +#line 3651 "parse.c" break; case 245: /* cmd ::= DROP TRIGGER ifexists fullname */ -#line 1470 "parse.y" +#line 1471 "parse.y" { sqlite3DropTrigger(pParse,yymsp[0].minor.yy387,yymsp[-1].minor.yy52); } -#line 3657 "parse.c" +#line 3658 "parse.c" break; case 246: /* cmd ::= REINDEX */ -#line 1477 "parse.y" +#line 1478 "parse.y" {sqlite3Reindex(pParse, 0, 0);} -#line 3662 "parse.c" +#line 3663 "parse.c" break; case 247: /* cmd ::= REINDEX nm */ -#line 1478 "parse.y" +#line 1479 "parse.y" {sqlite3Reindex(pParse, &yymsp[0].minor.yy0, 0);} -#line 3667 "parse.c" +#line 3668 "parse.c" break; case 248: /* cmd ::= REINDEX nm ON nm */ -#line 1479 "parse.y" +#line 1480 "parse.y" {sqlite3Reindex(pParse, &yymsp[-2].minor.yy0, &yymsp[0].minor.yy0);} -#line 3672 "parse.c" +#line 3673 "parse.c" break; case 249: /* cmd ::= ANALYZE */ -#line 1484 "parse.y" +#line 1485 "parse.y" {sqlite3Analyze(pParse, 0);} -#line 3677 "parse.c" +#line 3678 "parse.c" break; case 250: /* cmd ::= ANALYZE nm */ -#line 1485 "parse.y" +#line 1486 "parse.y" {sqlite3Analyze(pParse, &yymsp[0].minor.yy0);} -#line 3682 "parse.c" +#line 3683 "parse.c" break; case 251: /* cmd ::= ALTER TABLE fullname RENAME TO nm */ -#line 1490 "parse.y" +#line 1491 "parse.y" { sqlite3AlterRenameTable(pParse,yymsp[-3].minor.yy387,&yymsp[0].minor.yy0); } -#line 3689 "parse.c" +#line 3690 "parse.c" break; case 252: /* with ::= */ -#line 1513 "parse.y" +#line 1514 "parse.y" {yymsp[1].minor.yy151 = 0;} -#line 3694 "parse.c" +#line 3695 "parse.c" break; case 253: /* with ::= WITH wqlist */ -#line 1515 "parse.y" +#line 1516 "parse.y" { yymsp[-1].minor.yy151 = yymsp[0].minor.yy151; } -#line 3699 "parse.c" +#line 3700 "parse.c" break; case 254: /* with ::= WITH RECURSIVE wqlist */ -#line 1516 "parse.y" +#line 1517 "parse.y" { yymsp[-2].minor.yy151 = yymsp[0].minor.yy151; } -#line 3704 "parse.c" +#line 3705 "parse.c" break; case 255: /* wqlist ::= nm eidlist_opt AS LP select RP */ -#line 1518 "parse.y" +#line 1519 "parse.y" { yymsp[-5].minor.yy151 = sqlite3WithAdd(pParse, 0, &yymsp[-5].minor.yy0, yymsp[-4].minor.yy382, yymsp[-1].minor.yy279); /*A-overwrites-X*/ } -#line 3711 "parse.c" +#line 3712 "parse.c" break; case 256: /* wqlist ::= wqlist COMMA nm eidlist_opt AS LP select RP */ -#line 1521 "parse.y" +#line 1522 "parse.y" { yymsp[-7].minor.yy151 = sqlite3WithAdd(pParse, yymsp[-7].minor.yy151, &yymsp[-5].minor.yy0, yymsp[-4].minor.yy382, yymsp[-1].minor.yy279); } -#line 3718 "parse.c" +#line 3719 "parse.c" break; default: /* (257) input ::= ecmd */ yytestcase(yyruleno==257); @@ -3825,7 +3826,7 @@ static void yy_syntax_error( } else { sqlite3ErrorMsg(pParse, "near \"%T\": syntax error", &TOKEN); } -#line 3829 "parse.c" +#line 3830 "parse.c" /************ End %syntax_error code ******************************************/ sqlite3ParserARG_STORE; /* Suppress warning about unused %extra_argument variable */ } diff --git a/src/box/sql/parse.y b/src/box/sql/parse.y index 914fc53b8..11db58d94 100644 --- a/src/box/sql/parse.y +++ b/src/box/sql/parse.y @@ -1350,12 +1350,14 @@ cmd ::= createkw trigger_decl(A) BEGIN trigger_cmd_list(S) END(Z). { Token all; all.z = A.z; all.n = (int)(Z.z - A.z) + Z.n; + pParse->initiateTTrans = false; sqlite3FinishTrigger(pParse, S, &all); } trigger_decl(A) ::= TRIGGER ifnotexists(NOERR) nm(B) trigger_time(C) trigger_event(D) ON fullname(E) foreach_clause when_clause(G). { + pParse->initiateTTrans = false; sqlite3BeginTrigger(pParse, &B, C, D.a, D.b, E, G, NOERR); A = B; /*A-overwrites-T*/ } diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c index a194a6e72..96609b5ca 100644 --- a/src/box/sql/vdbe.c +++ b/src/box/sql/vdbe.c @@ -2920,7 +2920,6 @@ case OP_Savepoint: { } if (isSchemaChange) { sqlite3ExpirePreparedStatements(db); - sqlite3ResetAllSchemasOfConnection(db); user_session->sql_flags |= SQLITE_InternChanges; } } diff --git a/test/sql-tap/trigger1.test.lua b/test/sql-tap/trigger1.test.lua index 93dd3aaac..40daba4d8 100755 --- a/test/sql-tap/trigger1.test.lua +++ b/test/sql-tap/trigger1.test.lua @@ -1,6 +1,6 @@ #!/usr/bin/env tarantool test = require("sqltester") -test:plan(31) +test:plan(33) --!./tcltestrunner.lua -- The author disclaims copyright to this source code. In place of @@ -848,6 +848,29 @@ test:do_catchsql_test( -- }) +test:do_catchsql_test( + "trigger1-16.8", + [[ + BEGIN; + CREATE TRIGGER tr168 INSERT ON tA BEGIN + INSERT INTO t16 values(1); + END; + ]], { + 1, [[Space _trigger does not support multi-statement transactions]] +}) + +test:execsql [[ + ROLLBACK; +]] + +test:do_catchsql_test( + "trigger1-16.9", + [[ + BEGIN; + DROP TRIGGER t16err3; + ]], { + 1, [[Space _trigger does not support multi-statement transactions]] +}) -- MUST_WORK_TEST -- #------------------------------------------------------------------------- -- # Test that bug [34cd55d68e0e6e7c] has been fixed. diff --git a/test/sql-tap/trigger9.test.lua b/test/sql-tap/trigger9.test.lua index af8181811..840d184bf 100755 --- a/test/sql-tap/trigger9.test.lua +++ b/test/sql-tap/trigger9.test.lua @@ -66,10 +66,10 @@ test:do_execsql_test( test:do_execsql_test( "trigger9-1.2.1", [[ + CREATE TRIGGER trig1 BEFORE DELETE ON t1 BEGIN + INSERT INTO t2 VALUES(old.x); + END; BEGIN; - CREATE TRIGGER trig1 BEFORE DELETE ON t1 BEGIN - INSERT INTO t2 VALUES(old.x); - END; DELETE FROM t1; SELECT * FROM t2; ]], { @@ -101,10 +101,11 @@ test:do_execsql_test( test:do_execsql_test( "trigger9-1.3.1", [[ + DROP TRIGGER IF EXISTS trig1; + CREATE TRIGGER trig1 BEFORE DELETE ON t1 BEGIN + INSERT INTO t2 VALUES(old.x); + END; BEGIN; - CREATE TRIGGER trig1 BEFORE DELETE ON t1 BEGIN - INSERT INTO t2 VALUES(old.x); - END; DELETE FROM t1; SELECT * FROM t2; ]], { @@ -136,10 +137,11 @@ test:do_execsql_test( test:do_execsql_test( "trigger9-1.4.1", [[ + DROP TRIGGER IF EXISTS trig1; + CREATE TRIGGER trig1 BEFORE DELETE ON t1 WHEN old.x='1' BEGIN + INSERT INTO t2 VALUES(old.x); + END; BEGIN; - CREATE TRIGGER trig1 BEFORE DELETE ON t1 WHEN old.x='1' BEGIN - INSERT INTO t2 VALUES(old.x); - END; DELETE FROM t1; SELECT * FROM t2; ]], { @@ -171,10 +173,11 @@ test:do_execsql_test( test:do_execsql_test( "trigger9-1.5.1", [[ + DROP TRIGGER IF EXISTS trig1; + CREATE TRIGGER trig1 BEFORE UPDATE ON t1 BEGIN + INSERT INTO t2 VALUES(old.x); + END; BEGIN; - CREATE TRIGGER trig1 BEFORE UPDATE ON t1 BEGIN - INSERT INTO t2 VALUES(old.x); - END; UPDATE t1 SET y = ''; SELECT * FROM t2; ]], { @@ -206,10 +209,11 @@ test:do_execsql_test( test:do_execsql_test( "trigger9-1.6.1", [[ + DROP TRIGGER IF EXISTS trig1; + CREATE TRIGGER trig1 BEFORE UPDATE ON t1 BEGIN + INSERT INTO t2 VALUES(old.x); + END; BEGIN; - CREATE TRIGGER trig1 BEFORE UPDATE ON t1 BEGIN - INSERT INTO t2 VALUES(old.x); - END; UPDATE t1 SET y = ''; SELECT * FROM t2; ]], { @@ -241,10 +245,11 @@ test:do_execsql_test( test:do_execsql_test( "trigger9-1.7.1", [[ + DROP TRIGGER IF EXISTS trig1; + CREATE TRIGGER trig1 BEFORE UPDATE ON t1 WHEN old.x>='2' BEGIN + INSERT INTO t2 VALUES(old.x); + END; BEGIN; - CREATE TRIGGER trig1 BEFORE UPDATE ON t1 WHEN old.x>='2' BEGIN - INSERT INTO t2 VALUES(old.x); - END; UPDATE t1 SET y = ''; SELECT * FROM t2; ]], { @@ -290,10 +295,11 @@ test:do_execsql_test( "trigger9-3.2", [[ CREATE VIEW v1 AS SELECT * FROM t3; + DROP TRIGGER IF EXISTS trig1; + CREATE TRIGGER trig1 INSTEAD OF UPDATE ON v1 BEGIN + INSERT INTO t2 VALUES(old.a); + END; BEGIN; - CREATE TRIGGER trig1 INSTEAD OF UPDATE ON v1 BEGIN - INSERT INTO t2 VALUES(old.a); - END; UPDATE v1 SET b = 'hello'; SELECT * FROM t2; ROLLBACK; @@ -314,10 +320,11 @@ test:do_test( -- return test:execsql([[ CREATE VIEW v1 AS SELECT a, b AS c FROM t3 WHERE c > 'one'; + DROP TRIGGER IF EXISTS trig1; + CREATE TRIGGER trig1 INSTEAD OF UPDATE ON v1 BEGIN + INSERT INTO t2 VALUES(old.a); + END; BEGIN; - CREATE TRIGGER trig1 INSTEAD OF UPDATE ON v1 BEGIN - INSERT INTO t2 VALUES(old.a); - END; UPDATE v1 SET c = 'hello'; SELECT * FROM t2; ROLLBACK; @@ -333,11 +340,12 @@ test:do_execsql_test( "trigger9-3.4", [[ CREATE VIEW v1 AS SELECT DISTINCT a, b FROM t3; + DROP TRIGGER IF EXISTS trig1; + CREATE TRIGGER trig1 INSTEAD OF UPDATE ON v1 BEGIN + INSERT INTO t2 VALUES(old.a); + END; BEGIN; INSERT INTO t3 VALUES(4, 3, 'three'); - CREATE TRIGGER trig1 INSTEAD OF UPDATE ON v1 BEGIN - INSERT INTO t2 VALUES(old.a); - END; UPDATE v1 SET b = 'hello'; SELECT * FROM t2; ROLLBACK; @@ -352,11 +360,12 @@ test:do_execsql_test( "trigger9-3.5", [[ CREATE VIEW v1 AS SELECT a, b FROM t3 EXCEPT SELECT 1, 'one'; + DROP TRIGGER IF EXISTS trig1; + CREATE TRIGGER trig1 INSTEAD OF UPDATE ON v1 BEGIN + INSERT INTO t2 VALUES(old.a); + END; BEGIN; INSERT INTO t3 VALUES(5, 1, 'uno'); - CREATE TRIGGER trig1 INSTEAD OF UPDATE ON v1 BEGIN - INSERT INTO t2 VALUES(old.a); - END; UPDATE v1 SET b = 'hello'; SELECT * FROM t2; ROLLBACK; @@ -372,11 +381,12 @@ test:do_execsql_test( [[ CREATE VIEW v1 AS SELECT sum(a) AS a, max(b) AS b FROM t3 GROUP BY t3.a HAVING b>'two'; + DROP TRIGGER IF EXISTS trig1; + CREATE TRIGGER trig1 INSTEAD OF UPDATE ON v1 BEGIN + INSERT INTO t2 VALUES(old.a); + END; BEGIN; INSERT INTO t3 VALUES(6, 1, 'zero'); - CREATE TRIGGER trig1 INSTEAD OF UPDATE ON v1 BEGIN - INSERT INTO t2 VALUES(old.a); - END; UPDATE v1 SET b = 'hello'; SELECT * FROM t2; ROLLBACK; @@ -436,5 +446,4 @@ test:do_execsql_test( }) - test:finish_test() -- 2.11.0