1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84
|
#!/usr/bin/env tarantool
test = require("sqltester")
test:plan(20)
box.execute("DROP TABLE IF EXISTS t1")
box.execute("DROP TABLE IF EXISTS t2")
box.execute("CREATE TABLE t1 (s1 INT UNIQUE, s2 INT PRIMARY KEY);")
box.execute("CREATE TABLE t2 (s1 INT UNIQUE, s2 INT PRIMARY KEY);")
box.execute("INSERT INTO t2 VALUES (1,1);")
box.execute("INSERT INTO t1 VALUES (3,3);")
for _, prefix in pairs({"BEFORE", "AFTER"}) do
box.execute('DROP TRIGGER IF EXISTS t1i')
box.execute('CREATE TRIGGER t1i '..prefix..' INSERT ON t1 FOR EACH ROW \
BEGIN INSERT INTO t2 VALUES (1,1); END')
test:do_catchsql_test(prefix..'_insert1',
'INSERT INTO t1 VALUES(1, 2)',
{1,"Duplicate key exists in unique index 'pk_unnamed_T2_2' in space 'T2'"})
test:do_execsql_test(prefix..'_insert1_check1',
'SELECT * FROM t1',
{3, 3})
test:do_execsql_test(prefix..'_insert1_check2',
'SELECT * FROM t2',
{1, 1})
box.execute('DROP TRIGGER IF EXISTS t1u')
box.execute('CREATE TRIGGER t1u '..prefix..' UPDATE ON t1 FOR EACH ROW \
BEGIN INSERT INTO t2 VALUES (1,1); END')
test:do_catchsql_test(prefix..'_update1',
'UPDATE t1 SET s1=1',
{1,"Duplicate key exists in unique index 'pk_unnamed_T2_2' in space 'T2'"})
test:do_execsql_test(prefix..'_update1_check1',
'SELECT * FROM t1',
{3, 3})
test:do_execsql_test(prefix..'_insert1_check2',
'SELECT * FROM t2',
{1, 1})
box.execute('DROP TRIGGER IF EXISTS t1ds')
-- FOR EACH STATEMENT
box.execute('CREATE TRIGGER t1ds '..prefix..' DELETE ON t1 FOR EACH ROW\
BEGIN INSERT INTO t2 VALUES (2,2); \
INSERT INTO t2 VALUES (2,2); END')
test:do_catchsql_test(prefix..'delete1',
'DELETE FROM t1;',
{1, "Duplicate key exists in unique index 'pk_unnamed_T2_2' in space 'T2'"})
-- Nothing should be inserted due to abort
test:do_execsql_test('delete1_check1',
'SELECT * FROM t2',
{1, 1})
-- Nothing should be deleted
test:do_execsql_test('delete1_check2',
'SELECT * FROM t1',
{3, 3})
end
-- Check multi-insert
test:do_catchsql_test('insert2',
'INSERT INTO t1 VALUES (5, 6), (6, 7)',
{1, "Duplicate key exists in unique index 'pk_unnamed_T2_2' in space 'T2'"})
test:do_execsql_test('insert2_check',
'SELECT * FROM t1;',
{3, 3})
-- Cleanup
box.execute('DROP TRIGGER IF EXISTS t1i')
box.execute('DROP TRIGGER IF EXISTS t1u')
box.execute('DROP TRIGGER IF EXISTS t1ds')
box.execute('DROP TABLE t1')
box.execute('DROP TABLE t2')
test:finish_test()
|