
-- table created on Azure SQL Server
create table t1 (id bigint not null, title varchar(50) not null, qty int not null);

-- CONNECT table created in mariadb 10.1.13
CREATE TABLE MS_t1 ( `id` BIGINT NOT NULL , `title` VARCHAR(50) NOT NULL , `qty` BIGINT NOT NULL  )  
ENGINE=CONNECT TABLE_TYPE=JDBC  BLOCK_SIZE=250  TABNAME='t1'  CONNECTION='ds1496357478163' OPTION_LIST='Memory=2';

insert into MS_t1 (id, title, qty) values (1,'nut',0),(2,'bolt',0),(3,'screw',0);

-- create related local table in mariadb 10.1.13
create table t2 (id bigint not null, linked_id bigint not null, adj_qty int not null, name varchar(30), primary key (id) );

insert into t2 (id, linked_id, adj_qty) values (1,2,5), (2,3,1), (3,2,1);

select * from t2 join MS_t1 on (t2.linked_id = MS_t1.id);

CREATE OR REPLACE procedure a_test (v_id bigint)
BEGIN
	
	DECLARE v_old_qty, v_new_qty, v_inventory_qty_received BIGINT;
	
	DECLARE items CURSOR FOR
		SELECT t2.linked_id, t2.adj_qty
		FROM t2
		WHERE t2.linked_id = v_id;
	
	FOR item IN items DO
		
		SET v_inventory_qty_received = item.adj_qty;
		
		SELECT qty INTO v_old_qty
		FROM MS_t1 
		WHERE id = item.linked_id;
		
		SET v_new_qty = v_old_qty + v_inventory_qty_received;
		
		UPDATE MS_t1
		SET qty = v_new_qty
		WHERE id = item.linked_id;
		
	END FOR;
	
END


CREATE OR REPLACE TRIGGER `a_test_trigger` AFTER UPDATE ON `t2` FOR EACH ROW 
BEGIN
	call a_test(NEW.linked_id);
END

CALL a_test(2); -- this works - value should be 6 and is correct

UPDATE t2 SET name = 'new value' WHERE id = 1; -- error: Table 'MS_t1' is marked as crashed and should be repaired

-- RESET values for easy additional checking
UPDATE MS_t1 SET qty = 0;
