mySQL trigger protecting from insert

Question

CREATE TABLE IF NOT EXISTS types (
  id INT(4) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(80),
  INDEX(name)
) engine=InnoDB;

CREATE TABLE IF NOT EXISTS owners (
  id INT(4) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  first_name VARCHAR(30),
  last_name VARCHAR(30),
  address VARCHAR(255),
  city VARCHAR(80),
  telephone VARCHAR(20),
wallet DECIMAL(),
  INDEX(last_name)
) engine=InnoDB;

CREATE TABLE IF NOT EXISTS pets (
  id INT(4) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(30),
  birth_date DATE,
  type_id INT(4) UNSIGNED NOT NULL,
  owner_id INT(4) UNSIGNED NOT NULL,
  INDEX(name),
  FOREIGN KEY (owner_id) REFERENCES owners(id),
  FOREIGN KEY (type_id) REFERENCES types(id)
) engine=InnoDB;

CREATE TABLE IF NOT EXISTS visits (
  id INT(4) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  pet_id INT(4) UNSIGNED NOT NULL,
  visit_date DATE,
  description VARCHAR(255),
  FOREIGN KEY (pet_id) REFERENCES pets(id)
) engine=InnoDB;

Before that I only used mssql and there you could use rollback inside of trigger but there you can't do that. I want to write trigger that rollback/ or do not allow to set new visit for owner of pets who has 1000 debt

I even tried to write such simple trigger but it doesn't work either

CREATE TRIGGER wallet_check before INSERT ON visits 
FOR EACH ROW
    BEGIN

    IF(NEW.pet_id <3) then 
  INSERT INTO visits(id,visit_date,description,pet_id,vet_id)
VALUES (NEW.id, NEW.visit_date, NEW.description,NEW.pet_id,NEW.vet_id);


END IF;
END

I read that in mysql you use transactions for such situations but I don't know how to write that.

Have you seen https://stackoverflow.com/questions/2981930/mysql-trigger-to-prevent-insert-under-certain-conditions ? — Caius Jard, Nov 09 '18 at 15:29

score 0 · Accepted Answer · answered Nov 09 '18 at 16:28

You can use SET SIGNALSTATE to throw an exception in the BEFORE INSERT trigger. This will stop the execution and INSERT will not happen.

DELIMITER $$
CREATE TRIGGER wallet_check BEFORE INSERT ON visits 
FOR EACH ROW
    BEGIN

    IF(NEW.pet_id >= 3) THEN -- throw exception 
      SIGNAL SQLSTATE '45000' SET message_text = 'pet_id cannot be more than 3';
    END IF;
END $$
DELIMITER ;

mySQL trigger protecting from insert

1 Answers1