2

I am trying to convert some DB2 queries to MySQL . I have tried to find some tools to convert the conversion . Unfortunately i din't find any conversions tools and some of the online tools are not converting properly. So i have tried to convert DB2 queries myself to MySQL .

I have started to create 3 trigger queries in MySQL . Unfortunately i am getting some syntax errors . These are the following queries were i migrated from DB2 to MySQL .

Trigger 1 :

CREATE TRIGGER SAMPLE_TABLE3 AFTER INSERT ON SAMPLE_TABLE4
  FOR EACH ROW
  BEGIN
      INSERT INTO LICENSE_REVISION(LICENSE, LICENSE_REV) VALUES (NEW.ID, (NEXT VALUE FOR REVISION));
  END;

Here i am getting syntax error 

right syntax to use near 'VALUE FOR REVISION));
  END' at line 9

I think the problem due to NEXT VALUE FOR .

Question 1 :

Is it any NEXT VALUE FOR alternative for MySQL ?

In trigger 2 , i am doing 

CREATE TRIGGER SAMPLE_TRIGGER AFTER UPDATE OF SUPPORT__SERVER_UPS ON SETTINGS
  FOR EACH ROW
  BEGIN ATOMIC
    FOR d AS SELECT ID AS D_ID FROM DOMAIN
    DO UPDATE DOMAIN_REVISION DR SET DR.SERVER_DOMAIN_REV = (NEXT VALUE FOR REVISION) WHERE DR.DOMAIN = D_ID;
    END FOR;
  END;

and getting error : 

right syntax to use near 'OF SUPPORT__SERVER_UPS ON SETTINGS
  REFERENCING NEW AS NEW OLD AS OLD
  FOR EAC' at line 6

i think the problem is AFTER UPDATE OF with column and i am suspecting FOR d AS line 4 and BEGIN ATOMIC too .

Question 2 :

  1. Can i use AFTER UPDATE OF with column in MySQL ? and any mistakes in FOR d AS line no 4 and BEGIN ATOMIC line 3 .
  2. if i have use multiple column values like

trigger :

  CREATE TRIGGER SAMPLE_4 AFTER UPDATE OF IPV4_FIRST, IPV4, IPV4_MASK, IPV6_FIRST, IPV6, IPV6_MASK ON VIRTUAL_NETWORK
          FOR EACH ROW
          BEGIN
          FOR d AS SELECT DOMAIN AS D_ID FROM DOMAIN_VIRTUAL_NETWORK WHERE VIRTUAL_NETWORK=NEW.ID
            DO UPDATE DOMAIN_REVISION DR SET DR.CLIENT_NETWORK_REV = (NEXT VALUE FOR REVISION) WHERE DR.DOMAIN = D_ID;
            END FOR;
          END;

How can i approach this problem ? Should i use like this 

IF NEW.a <> OLD.a or NEW.b <> OLD.b ?

I am beginner in this and i want to help to migrate . I know these are syntax problem and not valid questions . Please help or suggestion .

Update :

I have tried with last_insert_id and CURSOR . This is the working code now :

  CREATE TRIGGER SAMPLE_TRIGGER AFTER UPDATE ON SETTINGS
  FOR EACH ROW
  BEGIN
  DECLARE my_cursor CURSOR FOR  SELECT ID AS D_ID FROM DOMAIN;
  IF NEW.SUPPORT__SERVER_UPS <=> OLD.SUPPORT__SERVER_UPS THEN
  open my_cursor; 
  my_loop: loop
    FETCH my_cursor INTO D_ID;
    UPDATE DOMAIN_REVISION DR SET DR.SERVER_DOMAIN_REV = (last_insert_id()) WHERE DR.DOMAIN = D_ID;
    end loop my_loop; 
    close my_cursor;
   END IF;
  END;
Improve this question
2
  • NEXT VALUE FOR REVISION looks like it replaceable with SELECT MAX(REVISION) + 1 FROM [table] Commented Nov 27, 2017 at 15:52
  • Thanks . I will try it now . Any suggestion for other query ? FYI : I am using MySQL 5.7 Commented Nov 27, 2017 at 15:53

1 Answer 1

Reset to default
1

Sequences

MySQL doesn't have sequence objects. NEXT VALUE FOR <sequence> is not supported in MySQL.

There's an awkward workaround to simulate a sequence by creating a single table that you increment every time you want a new value.

mysql> create table sequence (id int not null primary key );
mysql> insert into sequence values (0);
mysql> update sequence set id = last_insert_id(id+1);
mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                1 |
+------------------+

But every time you would use NEXT VALUE FOR <sequence> you would repeat the last two steps above. The last_insert_id() function in MySQL is more or less like DB2's IDENTITY_VAL_LOCAL(). Read full docs on last_insert_id() function here: https://dev.mysql.com/doc/refman/5.7/en/information-functions.html#function_last-insert-id

Column triggers

MySQL trigger declaration syntax is like

CREATE TRIGGER SAMPLE_TRIGGER AFTER UPDATE ON SETTINGS
FOR EACH ROW ...

MySQL supports no "OF" clause. That's used in DB2 for triggering on the update of specific columns, but MySQL doesn't support triggers on specific columns.

Loops

The FOR d AS SELECT ... loop construct is not supported by MySQL. If you need to do a loop, you need to learn about CURSOR syntax. See examples at https://dev.mysql.com/doc/refman/5.7/en/cursors.html

Improve this answer
Sign up to request clarification or add additional context in comments.

17 Comments

Thanks for valuable information !!!! . I have one query . If MySQL doesn't support triggers on specific columns , how can i write that functionality? Is it any other methods to achieve that functionality ?
Also can i achieve this functionality with condition for column in trigger like if NEW.column1 <=> OLD.column1 THEN ?
The trigger will fire on UPDATE if any column changes (like a DB2 trigger if you omit the ON <column> clause). So yes, you would have to add IF/THEN statements in the trigger body.
Thank you for valuable suggestions and time . I will work on the IF/THEN statements .
Porting between any two RDBMS products is bound to be a long, complicated process. Despite SQL being an industry standard, each implementation adds a lot of their own custom features and extensions. In particular, the procedure / trigger language for each RDBMS is quite different. So you will probably have to do a lot of reading of manuals, and a lot of testing.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.