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

    update triggers for view

    rastegar
    rastegar Jun 13, 2017 6:59 PM

      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...