env = require('test_run')
---
...
test_run = env.new()
---
...
engine = test_run:get_cfg('engine')
---
...
_ = box.space._session_settings:update('sql_default_engine', {{'=', 2, engine}})
---
...
-- Get invariant part of the tuple; name and opts don't change.
 function immutable_part(data) local r = {} for i, l in pairs(data) do table.insert(r, {l.name, l.opts}) end return r end
---
...
--
-- gh-3273: Move Triggers to server
--
box.execute("CREATE TABLE t1(x INTEGER PRIMARY KEY);")
---
- row_count: 1
...
box.execute("CREATE TABLE t2(x INTEGER PRIMARY KEY);")
---
- row_count: 1
...
box.execute([[CREATE TRIGGER t1t AFTER INSERT ON t1 FOR EACH ROW BEGIN INSERT INTO t2 VALUES(1); END; ]])
---
- row_count: 1
...
immutable_part(box.space._trigger:select())
---
- - - T1T
    - {'sql': 'CREATE TRIGGER t1t AFTER INSERT ON t1 FOR EACH ROW BEGIN INSERT INTO
        t2 VALUES(1); END; '}
...
space_id = box.space._space.index["name"]:get('T1').id
---
...
-- Checks for LUA tuples.
tuple = {"T1t", space_id, {sql = "CREATE TRIGGER t1t AFTER INSERT ON t1 FOR EACH ROW BEGIN INSERT INTO t2 VALUES(1); END;"}}
---
...
box.space._trigger:insert(tuple)
---
- error: 'Failed to execute SQL statement: trigger name does not match extracted from
    SQL'
...
tuple = {"T1t", space_id, {sql = "CREATE TRIGGER t12t AFTER INSERT ON t1 FOR EACH ROW BEGIN INSERT INTO t2 VALUES(1); END;"}}
---
...
box.space._trigger:insert(tuple)
---
- error: 'Failed to execute SQL statement: trigger name does not match extracted from
    SQL'
...
tuple = {"T2T", box.space.T1.id + 1, {sql = "CREATE TRIGGER t2t AFTER INSERT ON t1 FOR EACH ROW BEGIN INSERT INTO t2 VALUES(1); END;"}}
---
...
box.space._trigger:insert(tuple)
---
- error: 'Failed to execute SQL statement: trigger space_id does not match the value
    resolved on AST building from SQL'
...
immutable_part(box.space._trigger:select())
---
- - - T1T
    - {'sql': 'CREATE TRIGGER t1t AFTER INSERT ON t1 FOR EACH ROW BEGIN INSERT INTO
        t2 VALUES(1); END; '}
...
box.execute("DROP TABLE T1;")
---
- row_count: 1
...
immutable_part(box.space._trigger:select())
---
- []
...
box.execute("CREATE TABLE t1(x INTEGER PRIMARY KEY);")
---
- row_count: 1
...
box.execute([[CREATE TRIGGER t1t AFTER INSERT ON t1 FOR EACH ROW BEGIN INSERT INTO t2 VALUES(1); END; ]])
---
- row_count: 1
...
immutable_part(box.space._trigger:select())
---
- - - T1T
    - {'sql': 'CREATE TRIGGER t1t AFTER INSERT ON t1 FOR EACH ROW BEGIN INSERT INTO
        t2 VALUES(1); END; '}
...
space_id = box.space._space.index["name"]:get('T1').id
---
...
-- Test, didn't trigger t1t degrade.
box.execute("INSERT INTO t1 VALUES(1);")
---
- row_count: 1
...
box.execute("SELECT * FROM t2;")
---
- metadata:
  - name: X
    type: integer
  rows:
  - [1]
...
box.execute("DELETE FROM t2;")
---
- row_count: 1
...
-- Test triggers.
tuple = {"T2T", space_id, {sql = "CREATE TRIGGER t2t AFTER INSERT ON t1 FOR EACH ROW BEGIN INSERT INTO t2 VALUES(2); END;"}}
---
...
_ = box.space._trigger:insert(tuple)
---
...
tuple = {"T3T", space_id, {sql = "CREATE TRIGGER t3t AFTER INSERT ON t1 FOR EACH ROW BEGIN INSERT INTO t2 VALUES(3); END;"}}
---
...
_ = box.space._trigger:insert(tuple)
---
...
immutable_part(box.space._trigger:select())
---
- - - T1T
    - {'sql': 'CREATE TRIGGER t1t AFTER INSERT ON t1 FOR EACH ROW BEGIN INSERT INTO
        t2 VALUES(1); END; '}
  - - T2T
    - {'sql': 'CREATE TRIGGER t2t AFTER INSERT ON t1 FOR EACH ROW BEGIN INSERT INTO
        t2 VALUES(2); END;'}
  - - T3T
    - {'sql': 'CREATE TRIGGER t3t AFTER INSERT ON t1 FOR EACH ROW BEGIN INSERT INTO
        t2 VALUES(3); END;'}
...
box.execute("INSERT INTO t1 VALUES(2);")
---
- row_count: 1
...
box.execute("SELECT * FROM t2;")
---
- metadata:
  - name: X
    type: integer
  rows:
  - [1]
  - [2]
  - [3]
...
box.execute("DELETE FROM t2;")
---
- row_count: 3
...
-- Test t1t after t2t and t3t drop.
box.execute("DROP TRIGGER T2T;")
---
- row_count: 1
...
_ = box.space._trigger:delete("T3T")
---
...
immutable_part(box.space._trigger:select())
---
- - - T1T
    - {'sql': 'CREATE TRIGGER t1t AFTER INSERT ON t1 FOR EACH ROW BEGIN INSERT INTO
        t2 VALUES(1); END; '}
...
box.execute("INSERT INTO t1 VALUES(3);")
---
- row_count: 1
...
box.execute("SELECT * FROM t2;")
---
- metadata:
  - name: X
    type: integer
  rows:
  - [1]
...
box.execute("DELETE FROM t2;")
---
- row_count: 1
...
-- Insert new SQL t2t and t3t.
box.execute([[CREATE TRIGGER t2t AFTER INSERT ON t1 FOR EACH ROW BEGIN INSERT INTO t2 VALUES(2); END; ]])
---
- row_count: 1
...
box.execute([[CREATE TRIGGER t3t AFTER INSERT ON t1 FOR EACH ROW BEGIN INSERT INTO t2 VALUES(3); END; ]])
---
- row_count: 1
...
immutable_part(box.space._trigger:select())
---
- - - T1T
    - {'sql': 'CREATE TRIGGER t1t AFTER INSERT ON t1 FOR EACH ROW BEGIN INSERT INTO
        t2 VALUES(1); END; '}
  - - T2T
    - {'sql': 'CREATE TRIGGER t2t AFTER INSERT ON t1 FOR EACH ROW BEGIN INSERT INTO
        t2 VALUES(2); END; '}
  - - T3T
    - {'sql': 'CREATE TRIGGER t3t AFTER INSERT ON t1 FOR EACH ROW BEGIN INSERT INTO
        t2 VALUES(3); END; '}
...
box.execute("INSERT INTO t1 VALUES(4);")
---
- row_count: 1
...
box.execute("SELECT * FROM t2;")
---
- metadata:
  - name: X
    type: integer
  rows:
  - [1]
  - [2]
  - [3]
...
-- Clean up.
box.execute("DROP TABLE t1;")
---
- row_count: 1
...
box.execute("DROP TABLE t2;")
---
- row_count: 1
...
immutable_part(box.space._trigger:select())
---
- []
...
-- Test target tables restricts.
box.execute("CREATE TABLE t1(a INT PRIMARY KEY,b INT);")
---
- row_count: 1
...
space_id = box.space.T1.id
---
...
tuple = {"T1T", space_id, {sql = [[create trigger t1t instead of update on t1 for each row begin delete from t1 WHERE a=old.a+2; end;]]}}
---
...
box.space._trigger:insert(tuple)
---
- error: 'Failed to execute SQL statement: cannot create INSTEAD OF trigger on space:
    T1'
...
box.execute("CREATE VIEW V1 AS SELECT * FROM t1;")
---
- row_count: 1
...
space_id = box.space.V1.id
---
...
tuple = {"V1T", space_id, {sql = [[create trigger v1t before update on v1 for each row begin delete from t1 WHERE a=old.a+2; end;]]}}
---
...
box.space._trigger:insert(tuple)
---
- error: 'Failed to execute SQL statement: cannot create BEFORE trigger on view: V1'
...
tuple = {"V1T", space_id, {sql = [[create trigger v1t AFTER update on v1 for each row begin delete from t1 WHERE a=old.a+2; end;]]}}
---
...
box.space._trigger:insert(tuple)
---
- error: 'Failed to execute SQL statement: cannot create AFTER trigger on view: V1'
...
space_id =  box.space._fk_constraint.id
---
...
tuple = {"T1T", space_id, {sql = [[create trigger t1t instead of update on "_fk_constraint" for each row begin delete from t1 WHERE a=old.a+2; end;]]}}
---
...
box.space._trigger:insert(tuple)
---
- error: 'Failed to execute SQL statement: cannot create trigger on system table'
...
box.execute("DROP VIEW V1;")
---
- row_count: 1
...
box.execute("DROP TABLE T1;")
---
- row_count: 1
...
--
-- gh-3531: Assertion with trigger and two storage engines
--
-- Case 1: Src 'vinyl' table; Dst 'memtx' table
box.space._session_settings:update('sql_default_engine', {{'=', 2, 'vinyl'}})
---
- ['sql_default_engine', 'vinyl']
...
box.execute("CREATE TABLE m (s0 INT PRIMARY KEY, s1 TEXT UNIQUE);")
---
- row_count: 1
...
box.execute("CREATE TRIGGER m1 BEFORE UPDATE ON m FOR EACH ROW BEGIN UPDATE n SET s2 = 'now'; END;")
---
- row_count: 1
...
box.space._session_settings:update('sql_default_engine', {{'=', 2, 'memtx'}})
---
- ['sql_default_engine', 'memtx']
...
box.execute("CREATE TABLE n (s0 INT PRIMARY KEY, s1 TEXT UNIQUE, s2 NUMBER);")
---
- row_count: 1
...
box.execute("INSERT INTO m VALUES (0, '0');")
---
- row_count: 1
...
box.execute("INSERT INTO n VALUES (0, '',null);")
---
- row_count: 1
...
box.execute("UPDATE m SET s1 = 'The Rain In Spain';")
---
- null
- A multi-statement transaction can not use multiple storage engines
...
-- ANALYZE banned in gh-4069
-- box.sql.execute("ANALYZE m;")
box.execute("DROP TABLE m;")
---
- row_count: 1
...
box.execute("DROP TABLE n;")
---
- row_count: 1
...
-- Case 2: Src 'memtx' table; Dst 'vinyl' table
box.space._session_settings:update('sql_default_engine', {{'=', 2, 'memtx'}})
---
- ['sql_default_engine', 'memtx']
...
box.execute("CREATE TABLE m (s0 INT PRIMARY KEY, s1 TEXT UNIQUE);")
---
- row_count: 1
...
box.execute("CREATE TRIGGER m1 BEFORE UPDATE ON m FOR EACH ROW BEGIN UPDATE n SET s2 = 'now'; END;")
---
- row_count: 1
...
box.space._session_settings:update('sql_default_engine', {{'=', 2, 'vinyl'}})
---
- ['sql_default_engine', 'vinyl']
...
box.execute("CREATE TABLE n (s0 INT PRIMARY KEY, s1 TEXT UNIQUE, s2 NUMBER);")
---
- row_count: 1
...
box.execute("INSERT INTO m VALUES (0, '0');")
---
- row_count: 1
...
box.execute("INSERT INTO n VALUES (0, '',null);")
---
- row_count: 1
...
box.execute("UPDATE m SET s1 = 'The Rain In Spain';")
---
- null
- A multi-statement transaction can not use multiple storage engines
...
-- ANALYZE banned in gh-4069
-- box.sql.execute("ANALYZE n;")
box.execute("DROP TABLE m;")
---
- row_count: 1
...
box.execute("DROP TABLE n;")
---
- row_count: 1
...
-- Test SQL Transaction with LUA
box.space._session_settings:update('sql_default_engine', {{'=', 2, 'memtx'}})
---
- ['sql_default_engine', 'memtx']
...
box.execute("CREATE TABLE test (id INT PRIMARY KEY)")
---
- row_count: 1
...
box.space._session_settings:update('sql_default_engine', {{'=', 2, 'vinyl'}})
---
- ['sql_default_engine', 'vinyl']
...
box.execute("CREATE TABLE test2 (id INT PRIMARY KEY)")
---
- row_count: 1
...
box.execute("INSERT INTO test2 VALUES (2)")
---
- row_count: 1
...
box.execute("START TRANSACTION")
---
- row_count: 0
...
box.execute("INSERT INTO test VALUES (1)")
---
- row_count: 1
...
box.execute("SELECT * FROM test2")
---
- null
- A multi-statement transaction can not use multiple storage engines
...
box.execute("ROLLBACK;")
---
- row_count: 0
...
box.execute("DROP TABLE test;")
---
- row_count: 1
...
box.execute("DROP TABLE test2;")
---
- row_count: 1
...
--
-- gh-3536: Some triggers cause error messages and/or half-finished updates
--
box.execute("CREATE TABLE t (s1 INT, s2 INT, s3 INT, s4 INT PRIMARY KEY);")
---
- row_count: 1
...
box.execute("CREATE VIEW v AS SELECT s1, s2 FROM t;")
---
- row_count: 1
...
box.execute("CREATE TRIGGER tv INSTEAD OF UPDATE ON v FOR EACH ROW BEGIN UPDATE t SET s3 = new.s1 WHERE s1 = old.s1; END;")
---
- row_count: 1
...
box.execute("INSERT INTO t VALUES (1,1,1,1);")
---
- row_count: 1
...
box.execute("UPDATE v SET s2 = s1 + 1;")
---
- row_count: 0
...
box.execute("UPDATE v SET s1 = s1 + 5;")
---
- row_count: 0
...
box.execute("SELECT * FROM t;")
---
- metadata:
  - name: S1
    type: integer
  - name: S2
    type: integer
  - name: S3
    type: integer
  - name: S4
    type: integer
  rows:
  - [1, 1, 6, 1]
...
box.execute("DROP VIEW v;")
---
- row_count: 1
...
box.execute("DROP TABLE t;")
---
- row_count: 1
...
--
-- gh-3653: Dissallow bindings for DDL
--
box.execute("CREATE TABLE t1(a INT PRIMARY KEY, b INT);")
---
- row_count: 1
...
space_id = box.space.T1.id
---
...
box.execute("CREATE TRIGGER tr1 AFTER INSERT ON t1 FOR EACH ROW WHEN new.a = ? BEGIN SELECT 1; END;")
---
- null
- 'At line 1 at or near position 67: bindings are not allowed in DDL'
...
tuple = {"TR1", space_id, {sql = [[CREATE TRIGGER tr1 AFTER INSERT ON t1 FOR EACH ROW WHEN new.a = ? BEGIN SELECT 1; END;]]}}
---
...
box.space._trigger:insert(tuple)
---
- error: 'At line 1 at or near position 67: bindings are not allowed in DDL'
...
box.execute("DROP TABLE t1;")
---
- row_count: 1
...
-- 
-- Check that FOR EACH ROW clause is moandatory
--
box.execute("CREATE TABLE t1(a INT PRIMARY KEY, b INT);")
---
- row_count: 1
...
space_id = box.space.T1.id
---
...
box.execute("CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN ; END;")
---
- null
- 'At line 1 at or near position 39: FOR EACH STATEMENT triggers are not implemented,
  please supply FOR EACH ROW clause'
...
box.execute("DROP TABLE t1;")
---
- row_count: 1
...
--
-- gh-3570: Use box_space_id_by_name() instead of schema_find_id()
-- in SQL
--
box.schema.user.create('tester')
---
...
box.schema.user.grant('tester','read,write,create,execute', 'space', '_trigger')
---
...
box.execute("CREATE TABLE t1(x INTEGER PRIMARY KEY AUTOINCREMENT);")
---
- row_count: 1
...
box.session.su('tester')
---
...
--
-- Ensure that the CREATE TRIGGER statement cannot be executed if
-- the user does not have enough rights. In this case, the user
-- does not have rights to read from _space.
--
box.execute([[CREATE TRIGGER r1 AFTER INSERT ON t1 FOR EACH ROW BEGIN SELECT 1; END; ]])
---
- null
- Space 'T1' does not exist
...
box.session.su('admin')
---
...
box.schema.user.drop('tester')
---
...
box.execute("DROP TABLE t1;")
---
- row_count: 1
...
--
-- gh-4188: make sure that the identifiers that were generated
-- during the INSERT performed by the triggers are not returned.
--
box.execute('CREATE TABLE t1 (i INT PRIMARY KEY AUTOINCREMENT);')
---
- row_count: 1
...
box.execute('CREATE TABLE t2 (i INT PRIMARY KEY AUTOINCREMENT);')
---
- row_count: 1
...
box.execute('CREATE TABLE t3 (i INT PRIMARY KEY AUTOINCREMENT);')
---
- row_count: 1
...
box.execute('CREATE TRIGGER r1 AFTER INSERT ON t2 FOR EACH ROW BEGIN INSERT INTO t1 VALUES (null); END')
---
- row_count: 1
...
box.execute('INSERT INTO t1 VALUES (100);')
---
- row_count: 1
...
box.execute('INSERT INTO t2 VALUES (NULL), (NULL), (NULL);')
---
- autoincrement_ids:
  - 1
  - 2
  - 3
  row_count: 3
...
box.execute('SELECT * FROM t1;')
---
- metadata:
  - name: I
    type: integer
  rows:
  - [100]
  - [101]
  - [102]
  - [103]
...
box.execute('SELECT * FROM t2;')
---
- metadata:
  - name: I
    type: integer
  rows:
  - [1]
  - [2]
  - [3]
...
box.execute('CREATE TRIGGER r2 AFTER INSERT ON t3 FOR EACH ROW BEGIN INSERT INTO t2 VALUES (null); END')
---
- row_count: 1
...
box.execute('INSERT INTO t3 VALUES (NULL), (NULL), (NULL);')
---
- autoincrement_ids:
  - 1
  - 2
  - 3
  row_count: 3
...
box.execute('SELECT * FROM t1;')
---
- metadata:
  - name: I
    type: integer
  rows:
  - [100]
  - [101]
  - [102]
  - [103]
  - [104]
  - [105]
  - [106]
...
box.execute('SELECT * FROM t2;')
---
- metadata:
  - name: I
    type: integer
  rows:
  - [1]
  - [2]
  - [3]
  - [4]
  - [5]
  - [6]
...
box.execute('SELECT * FROM t3;')
---
- metadata:
  - name: I
    type: integer
  rows:
  - [1]
  - [2]
  - [3]
...
box.execute('DROP TABLE t1;')
---
- row_count: 1
...
box.execute('DROP TABLE t2;')
---
- row_count: 1
...
box.execute('DROP TABLE t3;')
---
- row_count: 1
...