Increase value by 1 upon adding new row

Hi,

Is it possible to automatically increase the value of a field when a new row is added to a table? For example, to increment the Compound ID by 1 when a new structure is added to a structure table?

Thanks,

Evert

Dear Evert,

as far as I know, it might not be possible to do it in IJC itself. Possible solution would be via SQL triggers and procedures.

On what database are you running? Derby?

If so, what we could do is that you could create a project with a given table in the way you desire it to look like (no confidential data needed - you could use some generic data) and we would modify it in the way that it works for you, adding a database trigger)

This way, it would be easier for us to understand the usecase and easier for you to get the solution.

There is also documentation on the topic here.

Wishing all the best

David

This is on mySQL.

Evert

Started solving the issue via email communication.

To keep the solution also for others on the forum, it is possible to do it with similar database trigger, while having a VARCHAR column in the table called TriggerID:

CREATE DEFINER=`ijc`@`%` TRIGGER custId
BEFORE INSERT
   ON SOME_TABLE FOR EACH ROW
   
BEGIN
   DECLARE cs varchar(50);
DECLARE num integer;
   -- Find username of person performing the INSERT into table
   SELECT max(TriggerID) FROM SOME_TABLE INTO cs;
SET num = CONVERT(substring(cs, 8), SIGNED INTEGER);
SET num = num + 1;
   
SET NEW.TriggerID = CONCAT("custId-", CAST(num AS char));
   
END

 

Hi,

I'm afraid I can't get the trigger to work. If I apply this to a table called 'STRUCTURES' I get an error stating that this table already exists.

Here's the trigger I adopted from your to apply to a table called STRUCTURES:

USE `transmed`;
DELIMITER $$
CREATE TRIGGER `custId` BEFORE INSERT ON `STRUCTURES` FOR EACH ROW
BEGIN
DECLARE cs varchar(50);
DECLARE num integer;
SELECT max(TriggerID) FROM `STRUCTURES` INTO cs;
SET num = CONVERT(substring(cs, 8), SIGNED INTEGER);
SET num = num + 1;  
SET NEW.TriggerID = CONCAT("custId-", CAST(num AS char));
END

ERROR 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET NEW.TriggerID = CONCAT("custId-", CAST(num AS char));
END' at line 8
SQL Statement:
CREATE TRIGGER `custId` BEFORE INSERT ON `STRUCTURES` FOR EACH ROW
BEGIN
DECLARE cs varchar(50);
DECLARE num integer;
SELECT max(TriggerID) FROM `STRUCTURES` INTO cs;
SET num = CONVERT(substring(cs, 8), SIGNED INTEGER);
SET num = num + 1;  
SET NEW.TriggerID = CONCAT("custId-", CAST(num AS char));
END

ERROR: Error when running failback script. Details follow.

ERROR 1050: Table 'STRUCTURES' already exists
SQL Statement:
CREATE TABLE `STRUCTURES` (
  `cd_id` int(11) NOT NULL AUTO_INCREMENT,
  `cd_structure` mediumblob NOT NULL,
  `cd_smiles` text,
  `cd_formula` varchar(100) DEFAULT NULL,
  `cd_sortable_formula` varchar(255) DEFAULT NULL,
  `cd_molweight` double DEFAULT NULL,
  `cd_hash` int(11) NOT NULL,
  `cd_flags` varchar(20) DEFAULT NULL,
  `cd_timestamp` datetime NOT NULL,
  `cd_pre_calculated` tinyint(1) NOT NULL DEFAULT '0',
  `cd_taut_hash` int(11) NOT NULL,
  `cd_taut_frag_hash` varchar(1000) DEFAULT NULL,
  `cd_screen_descriptor` varchar(1000) DEFAULT NULL,
  `cd_fp1` int(11) NOT NULL,
  `cd_fp2` int(11) NOT NULL,
  `cd_fp3` int(11) NOT NULL,
  `cd_fp4` int(11) NOT NULL,
  `cd_fp5` int(11) NOT NULL,
  `cd_fp6` int(11) NOT NULL,
  `cd_fp7` int(11) NOT NULL,
  `cd_fp8` int(11) NOT NULL,
  `cd_fp9` int(11) NOT NULL,
  `cd_fp10` int(11) NOT NULL,
  `cd_fp11` int(11) NOT NULL,
  `cd_fp12` int(11) NOT NULL,
  `cd_fp13` int(11) NOT NULL,
  `cd_fp14` int(11) NOT NULL,
  `cd_fp15` int(11) NOT NULL,
  `cd_fp16` int(11) NOT NULL,
  `TriggerID` varchar(1000) DEFAULT NULL,
  PRIMARY KEY (`cd_id`),
  KEY `STRUCTURES_hx` (`cd_hash`),
  KEY `STRUCTURES_fx` (`cd_sortable_formula`),
  KEY `STRUCTURES_px` (`cd_pre_calculated`),
  KEY `STRUCTURES_thx` (`cd_taut_hash`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

ERROR 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET NEW.TriggerID = CONCAT("custId-", CAST(num AS char));
END' at line 8
SQL Statement:
CREATE TRIGGER `custId` BEFORE INSERT ON `STRUCTURES` FOR EACH ROW
BEGIN
DECLARE cs varchar(50);
DECLARE num integer;
SELECT max(TriggerID) FROM `STRUCTURES` INTO cs;
SET num = CONVERT(substring(cs, 8), SIGNED INTEGER);
SET num = num + 1;  
SET NEW.TriggerID = CONCAT("custId-", CAST(num AS char));
END

ERROR: Error when running failback script. Details follow.

ERROR 1050: Table 'STRUCTURES' already exists
SQL Statement:
CREATE TABLE `STRUCTURES` (
  `cd_id` int(11) NOT NULL AUTO_INCREMENT,
  `cd_structure` mediumblob NOT NULL,
  `cd_smiles` text,
  `cd_formula` varchar(100) DEFAULT NULL,
  `cd_sortable_formula` varchar(255) DEFAULT NULL,
  `cd_molweight` double DEFAULT NULL,
  `cd_hash` int(11) NOT NULL,
  `cd_flags` varchar(20) DEFAULT NULL,
  `cd_timestamp` datetime NOT NULL,
  `cd_pre_calculated` tinyint(1) NOT NULL DEFAULT '0',
  `cd_taut_hash` int(11) NOT NULL,
  `cd_taut_frag_hash` varchar(1000) DEFAULT NULL,
  `cd_screen_descriptor` varchar(1000) DEFAULT NULL,
  `cd_fp1` int(11) NOT NULL,
  `cd_fp2` int(11) NOT NULL,
  `cd_fp3` int(11) NOT NULL,
  `cd_fp4` int(11) NOT NULL,
  `cd_fp5` int(11) NOT NULL,
  `cd_fp6` int(11) NOT NULL,
  `cd_fp7` int(11) NOT NULL,
  `cd_fp8` int(11) NOT NULL,
  `cd_fp9` int(11) NOT NULL,
  `cd_fp10` int(11) NOT NULL,
  `cd_fp11` int(11) NOT NULL,
  `cd_fp12` int(11) NOT NULL,
  `cd_fp13` int(11) NOT NULL,
  `cd_fp14` int(11) NOT NULL,
  `cd_fp15` int(11) NOT NULL,
  `cd_fp16` int(11) NOT NULL,
  `TriggerID` varchar(1000) DEFAULT NULL,
  PRIMARY KEY (`cd_id`),
  KEY `STRUCTURES_hx` (`cd_hash`),
  KEY `STRUCTURES_fx` (`cd_sortable_formula`),
  KEY `STRUCTURES_px` (`cd_pre_calculated`),
  KEY `STRUCTURES_thx` (`cd_taut_hash`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Dear Evert,

I would suggest looking at this problem together with the Admin Tool issue during the call

Bests

David