mardi 13 octobre 2015

Creating SQLite trigger in file with delimiter

I have a table and wish to ensure that it only contains one row. I therefore added a couple of triggers, but receive the following errors.

How should the delimiters be used and can I only have one delimiter around both of the triggers?

PS. Not part of the question, but would appreciate a comment whether I should be using BEFORE or AFTER to ensure I only have one row.

pi@raspberrypi /var/www $ sqlite3 testing.db < testing.sql
Error: near line 81: near "DELIMITER": syntax error
Error: near line 87: near "$$": syntax error
Error: near line 95: near "$$": syntax error
pi@raspberrypi /var/www $


CREATE TABLE IF NOT EXISTS `config` (
  `id` TINYINT NOT NULL DEFAULT 0,
  `subdomain` VARCHAR(45) NOT NULL,
  `timezone` CHAR(3) NOT NULL,
  `timeout` TINYINT NOT NULL,
  `hash_config` CHAR(32) NOT NULL,
  `hash_points` CHAR(32) NOT NULL,
  PRIMARY KEY (`id`));

DELIMITER $$
CREATE TRIGGER `config_insert_zero`
BEFORE INSERT ON `config`
FOR EACH ROW
BEGIN
   SET NEW.id=0;
END $$

CREATE TRIGGER `config_update_zero`
BEFORE UPDATE ON `config`
FOR EACH ROW
BEGIN
   SET NEW.id=0;
END $$

DELIMITER ;

Aucun commentaire:

Enregistrer un commentaire