11 Replies Latest reply on Jul 17, 2017 1:17 PM by Steven Hawkins

    update triggers for view

    Andreas Schwarz Newbie

      Hi there

       

      I have a vdb with a view consisting from 2 databases. I want to be able to perform UPDATE/DELETE/INSERT on this view, therefore I have to create some "INSTEAD OF"-triggers.

      The INSERT trigger works as intented.

      CREATE TRIGGER ON users INSTEAD OF INSERT AS 
           FOR EACH ROW 
                BEGIN ATOMIC
                     INSERT INTO db2.user2 (dummy_data, created_at, updated_at) VALUES (NEW.country, NOW(), NOW());
                 END;
      

       

      Only the ones for UPDATE/DELETE give me some headache.

       

            CREATE TRIGGER ON users INSTEAD OF UPDATE AS  
              FOR EACH ROW  
                IF (NEW.id <= (SELECT MAX(id) FROM db1.user1))  
                  BEGIN ATOMIC  
                    UPDATE db1.user1 SET dummy_data = NEW.country, updated_at = NOW() 
                      WHERE id = NEW.id;
                  END  
                ELSE IF (NEW.id > (SELECT MAX(id) FROM db1.user1))  
                  BEGIN ATOMIC  
                    UPDATE db2.user2 SET dummy_data = NEW.country, updated_at = NOW()
                      WHERE id = (NEW.id - (SELECT MAX(id) FROM db1.user1));
                  END;   
                       
            CREATE TRIGGER ON users INSTEAD OF DELETE AS  
              FOR EACH ROW  
                IF (OLD.id < (SELECT MAX(id) FROM db1.user1))  
                  BEGIN ATOMIC  
                    DELETE FROM db1.user1 
                      WHERE id = OLD.id;
                  END  
                ELSE IF (OLD.id = (SELECT MAX(id) FROM db1.user1))  
                  BEGIN ATOMIC  
                    UPDATE db1.user1 SET dummy_data = '', updated_at = NOW() 
                      WHERE id = OLD.id;
                  END  
                ELSE IF (OLD.id > (SELECT MAX(id) FROM db1.user1))  
                  BEGIN ATOMIC  
                    DELETE FROM db2.user2 
                     WHERE id = OLD.id;
                  END;
      

       

      When I run a update or delete (via SquirrelSQL -> teiid-JDBC), I get this exception in the server log:

       

      2017-06-13 22:40:17,516 WARN  [com.arjuna.ats.arjuna] (Worker3_QueryProcessorQueue38) b2/frYuE4sMn ARJUNA012140: Adding multiple last resources is disallowed. Trying to add LastResourceRecord(XAOnePhaseResource(LocalXAResourceImpl@326acbef[connectionListener=5855129f connectionManager=15f8dd5a warned=false currentXid=< formatId=131077, gtrid_length=29, bqual_length=36, tx_uid=0:ffffac110004:-61ed27df:59404c3a:23b, node_name=1, branch_uid=0:ffffac110004:-61ed27df:59404c3a:23e, subordinatenodename=null, eis_name=forgot eis name for: 3 > productName=MySQL productVersion=10.1.22-MariaDB-1~jessie jndiName=java:/db2-ds])), but already have LastResourceRecord(XAOnePhaseResource(LocalXAResourceImpl@46aefd71[connectionListener=3722464 connectionManager=53fc5e19 warned=false currentXid=< formatId=131077, gtrid_length=29, bqual_length=36, tx_uid=0:ffffac110004:-61ed27df:59404c3a:23b, node_name=1, branch_uid=0:ffffac110004:-61ed27df:59404c3a:23d, subordinatenodename=null, eis_name=forgot eis name for: 2 > productName=MySQL productVersion=10.1.22-MariaDB-1~jessie jndiName=java:/db1-ds]))
      

       

      In Squirrel, I get this message:

      Error: TEIID30328 Remote org.teiid.api.exception.query.ExpressionEvaluationException: TEIID30328 Unable to evaluate (SELECT MAX(g_0.id) FROM db1.user1 AS g_0): TEIID30504 db1: 0 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT MAX(g_0.`id`) FROM `db1`.`user1` AS g_0]
      

       

      The source databases are both mysql databases which use the mariadb jdbc driver. they are configured as non-xa-sources, as I have MyISAM as storage engine, which does not support xa-transactions anyway.

       

      I do not quite understand what's going on. Is something wrong with my IF/ELSE statements?

       

      Grateful for any hint...