MySQL Creating trigger on view - Error 1347
mysql, triggers, view
Solution
MySQL does not support triggers on views.
The way to do this task is to make `Koncerty` a table without keys, in order to then operate on it with trigger actions. It all worked out just as I wanted.
Also, making `UNIQUE CONSTRAINTS` on records in tables does the job for the unique record, because it pops up an error when trying to add anything alike.
Problem
I'm supposed to write a trigger that checks `BEFORE INSERT` whether the record already exists, and then take some actions. I'm stuck with this error returned on issuing an `INSERT` statement against `Koncerty` view: Error Code: 1347. 'BazyLista3.Koncerty' is not BASE TABLE Here's the code for view `Koncerty`: ``` CREATE VIEW Koncerty (`nazwa_klubu`, `adres_klubu`, `nazwa_zespolu`, `ilosc_czlonkow_zespolu`, `data_wystepu`) AS ( SELECT `nazwa_klubu`, `kb`.`adres`, `nazwa_zespolu`, `zs`.`ilosc_czlonkow`, `data_wystepu` FROM `Koncert` AS kc INNER JOIN `Klub` AS kb ON `kc`.`nazwa_klubu` = `kb`.`nazwa` INNER JOIN `Zespol` AS zs ON `kc`.`nazwa_zespolu` = `zs`.`nazwa` ); ``` And my trigger, where I have this error: ``` DROP TRIGGER IF EXISTS `before_koncerty_insert` DELIMITER $$ CREATE TRIGGER `before_koncerty_insert` BEFORE INSERT ON `Koncerty` FOR EACH ROW BEGIN DECLARE i INT DEFAULT 0; SELECT COUNT(*) INTO i FROM `Koncerty` WHERE `nazwa_klubu` = NEW.`nazwa_klubu` AND `adres_klubu` = NEW.`adres_klubu` AND `nazwa_zespolu` = NEW.`nazwa_zespolu` AND `ilosc_czlonkow_zespolu` = NEW.`ilosc_czlonkow_zespolu` AND `data_wystepu` = NEW.`data_wystepu`; IF i > 0 THEN SIGNAL SQLSTATE '58005' SET MESSAGE_TEXT = 'Blad! Taka krotka juz istnieje'; END IF; END $$ DELIMITER ; ``` Tables ``` CREATE TABLE IF NOT EXISTS `Klub` ( `nazwa` varchar(50) NOT NULL, `adres` varchar(70) NOT NULL, PRIMARY KEY (`nazwa`) ) ENGINE = InnoDB DEFAULT CHARSET=utf8; CREATE TABLE IF NOT EXISTS `Zespol` ( `nazwa` varchar(50) NOT NULL, `ilosc_czlonkow` int(3) NOT NULL, PRIMARY KEY (`nazwa`) ) ENGINE = InnoDB DEFAULT CHARSET=utf8; CREATE TABLE IF NOT EXISTS `Koncert` ( `nazwa_klubu` varchar(50) NOT NULL, `nazwa_zespolu` varchar(50) NOT NULL, `data_wystepu` datetime NOT NULL, FOREIGN KEY (`nazwa_klubu`) REFERENCES `Klub`(`nazwa`), FOREIGN KEY (`nazwa_zespolu`) REFERENCES `Zespol`(`nazwa`) ) ENGINE = InnoDB DEFAULT CHARSET=utf8; ``` How do I get around this issue?