CODEWITHSUNDEEP
mysqldatabasetriggers
Reynier
Reynier

Reputation: 2478

Update value each time new rows is created

I have two tables: config(last_inserted_id) and element(id) is there any chance to get the last inserted id any time when new rows are created in element table and execute a update in column last_inserted_id at config table?

I have wrote this:

CREATE TRIGGER UPDATE_CONFIG_VALUES AFTER INSERT ON element
    BEGIN
        UPDATE config SET last_inserted_id = last_insert_id();
    END;
END;

Is that right? What happen if I delete a row in element table? Should the value get updated in config table or not" How I avoid this?

config table

CREATE TABLE IF NOT EXISTS `cmplatform`.`isrl_config` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `logo_address` VARCHAR(250) NOT NULL,
  `name` VARCHAR(250) NOT NULL,
  `rif` VARCHAR(20) NOT NULL,
  `address` TEXT NOT NULL,
  `phone` VARCHAR(14) NOT NULL,
  `last_retention_number` INT NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB

Upvotes: 0

Views: 65

Answers (2)

LHA
LHA

Reputation: 9655

TEST this ( Not tested)

CREATE TRIGGER element_inserted_tg AFTER INSERT ON element
FOR EACH ROW
BEGIN
    DECLARE count INT;
    SELECT COUNT(1) INTO count FROM config;
    IF count == 1
        UPDATE config SET last_inserted_id = NEW.id;
    ELSE
        INSERT INTO config VALUES (NEW.id);
    END IF;
END;

CREATE TRIGGER element_deleted_tg AFTER DELETE ON element
FOR EACH ROW
BEGIN
    UPDATE config SET last_inserted_id = (SELECT MAX(id) FROM element);
END;

Upvotes: 1

LHA
LHA

Reputation: 9655

Trigger is traditional way to do this requirement but not unique way. Another way is process it in your data access code. Let say that you have a DAO method to create new Element, you can get the id of element created and update it into last_inserted_id. If you do this way, you have to makesure there is ONLY ONE THREAD calling insert element method at a time.

Upvotes: 0

Related Questions