CODEWITHSUNDEEP
mysqlsqltriggersdefault-value
Frithjof
Frithjof

Reputation: 2264

Set default value of field to auto_increment value

I have a table where I have an auto increment column id and another column ord.

My goal is to have a 'default' value for ord that is id. I am using a trigger (see below), but it always uses 0 instead of the id value.

When I remove the auto_increment from id, it works as it should. How can I set the 'default' value of a field to the one of an auto_increment field?

Table

CREATE TABLE IF NOT EXISTS `mytable` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ord` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

Trigger

CREATE TRIGGER `ord_ai` BEFORE INSERT ON `mytable`
 FOR EACH ROW IF NEW.ord IS NULL THEN
    SET NEW.ord := NEW.id;
END IF

Upvotes: 0

Views: 1932

Answers (1)

Ravinder Reddy
Ravinder Reddy

Reputation: 24002

Read the next AUTO_INCREMENT value of the id field from INFORMATION_SCHMEA.TABLES and use it for ord field.

delimiter //

drop trigger if exists ord_ai //

CREATE TRIGGER `ord_ai` BEFORE INSERT ON `mytable`
FOR EACH ROW 
BEGIN
  IF NEW.ord IS NULL THEN
    SET NEW.ord := ( SELECT AUTO_INCREMENT 
                       FROM INFORMATION_SCHEMA.TABLES
                      WHERE TABLE_NAME = 'mytable'
                        AND TABLE_SCHEMA = DATABASE() );
  END IF;
END;
//

delimiter ;

Upvotes: 1

Related Questions