CODEWITHSUNDEEP
mysqlsql
kaktusas2598
kaktusas2598

Reputation: 691

Trigger not working on MySQL

I am trying to create to trigger to divide my primary key into two groups.

Here is my table: 

CREATE TABLE `q_locations` (
 `id` int(11) NOT NULL,
 `name` varchar(300) NOT NULL,
 `standalone` bit(1) NOT NULL DEFAULT b'0',
 UNIQUE KEY `id` (`id`),
 KEY `standalone` (`standalone`)
)

If standalone is 0, id should start from 1, if standalone = 1, id should start from 1000. Id should be increment after each insert.

My trigger: 

DELIMITER $$
CREATE TRIGGER trigger_insert_q_locations
BEFORE INSERT ON q_locations
FOR EACH ROW
BEGIN
    SET New.id = (
    SELECT coalesce(max(id) + 1, (case when standalone = 0 then 1 else 1000 end))  
    FROM q_locations 
    WHERE standalone = NEW.standalone);
END;

Update: So with help I got, I managed to insert trigger without no errors, bu when I update my locations table, trigger doesn't do anything. Values just keep incrementing as default.

Upvotes: 0

Views: 64

Answers (1)

dnlkng
dnlkng

Reputation: 839

Try:

SET NEW.id = (SELECT coalesce(...) ...)

Maybe SELECT INTO isn't working properly in Update Triggers with NEW-Aliases.

UPDATE:

This should work:

DELIMITER $$
CREATE TRIGGER trigger_insert_q_locations
BEFORE INSERT ON q_locations
FOR EACH ROW
BEGIN
  DECLARE currentid INT;
  SET currentid = (SELECT max(id) FROM q_locations WHERE standalone = NEW.standalone);

  IF NEW.standalone = 0 THEN 
    SET NEW.id = coalesce(currentid + 1, 1);
  ELSE 
    SET NEW.id = coalesce(currentid + 1,1000);
  END IF;
END;

Upvotes: 1

Related Questions