MySQL Creating trigger on view - Error 1347

Question

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?

Answer 1

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.

Answer 2

MySQL does not support triggers on views, from MySQL reference manual:

You cannot associate a trigger with a TEMPORARY table or a view.