Trigger to add value plus 1

[poodleinplad]

I want to use a trigger to insert the highest value and add 1.

Exampel db:

(Columns:)    id --- col1

(Row value ) 1  --- 567

 

I want the trigger before (?) insert to check highest number in col1 (example above 567), add 1 and update new rows col1 with the value 568. I have not figured out a way to do this with a trigger but I wish it could work somehow. I have a reason why I can't do this with php in my application.

 

This is what I've come up so far, but it isn't working. It only returns 100000 for some reason?

 

DELIMITER |

CREATE TRIGGER test_trigg BEFORE INSERT ON table1  
FOR EACH ROW BEGIN
SET NEW.col1 = (SELECT col1 + 1 FROM table1 ORDER BY column LIMIT 1);  
  END;
|

DELIMITER ;

[Jessica]

Make the column auto-incrementing

[poodleinplad]

But I already have auto-increment on the id-column. I can't have two auto-increment in the same table, can I? I am not an expert on these things.

[Jessica]

Did you try? I don't see why you couldn't.

[poodleinplad]

If you mean setting the col1 field to auto-increment I have tried this and gotten the the error message:

 

#1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key

 

Can I in some other way force auto-increment on another field by a trigger or something?

[Andy-H]

Why don't you just use the auto-increment field? The data is already there.

[poodleinplad]

For whatever reason I don't wish to do so. I know that this is not a "normal" way of doing this but I'm sure there is some way of accomplishing it.

[mikosiko]

something like this should work

DELIMITER $$
CREATE TRIGGER test_trigg
BEFORE INSERT ON  table1
  FOR EACH ROW
    BEGIN
      SET @nc = (SELECT MAX(col1) + 1 FROM table1);
      IF (ISNULL(@nc)) THEN
          SET @nc = 1;
     END IF;

    SET NEW.col1 = @nc;
   END
$$
DELIMITER ;

[poodleinplad]

Sorry for the delayed answer, there has been an holiday in Sweden.

 

Mikosiko, your trigger example worked perfectly but not on the column I desired. Does max() require the column to be int? It's wierd because the column (varchar) I wanted to use it on returns 100000  everytime while the test on the id column returns expected results. I want to learn more about this.

[poodleinplad]

Nevermind, I googled it and found that:

 

DELIMITER $$
CREATE TRIGGER test_trigg
BEFORE INSERT ON  table1
  FOR EACH ROW
    BEGIN
      SET @nc = (SELECT MAX(CAST(link_name AS UNSIGNED)) + 1 FROM table1);
      IF (ISNULL(@nc)) THEN
          SET @nc = 1;
     END IF;

    SET NEW.col1 = @nc;
   END
$$
DELIMITER ;

 

By adding MAX(CAST(link_name AS UNSIGNED)) it worked.  I don't know why though, but maybe it can be helpful to others. Thanks for the help everybody!

[cpd]

Nevermind, I googled it and found that:

 

DELIMITER $$
CREATE TRIGGER test_trigg
BEFORE INSERT ON  table1
  FOR EACH ROW
    BEGIN
      SET @nc = (SELECT MAX(CAST(link_name AS UNSIGNED)) + 1 FROM table1);
      IF (ISNULL(@nc)) THEN
          SET @nc = 1;
     END IF;

    SET NEW.col1 = @nc;
   END
$$
DELIMITER ;

 

By adding MAX(CAST(link_name AS UNSIGNED)) it worked.  I don't know why though, but maybe it can be helpful to others. Thanks for the help everybody!

 

Not 100% sure what your values are as MAX should be able to compare strings as well. Casting it as an unsigned value means its an integer which cannot be negative. It comes down to how the bits represent the number.