CODEWITHSUNDEEP
mysqlviewtriggers
Kamil Gosciminski
Kamil Gosciminski

Reputation: 17177

MySQL Creating trigger on view - Error 1347

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?

Upvotes: 9

Views: 18002

Answers (3)

Hooman Bahreini
Hooman Bahreini

Reputation: 15569

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

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

Upvotes: 1

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17177

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.

Upvotes: 6

rajug
rajug

Reputation: 77

The error might come if the view has used the database name in it. For example if a view has used a table as <dbname>.<tablename>:

create or replace view dealer_current_stage as
    select 
        dsc.dealer_id,
        dsc.new_stage current_stage,
        dsc.change_date last_stage_change_date
    from
        dealer_current_stage_pre dcsp
            left join
        risk.dealer_stage_changes dsc ON dsc.change_date = dcsp.max_stage_change_date
            and dsc.dealer_id = dcsp.dealer_id
    group by dsc.dealer_id;

Here, risk is a different database and its table is being used. So better do not use the separate database and table, if you have to use it then consider the privileges, etc.

Upvotes: -2

Related Questions