CODEWITHSUNDEEP
mysqlpostgresqltriggers
Nikola
Nikola

Reputation: 172

Trigger from plpgSQL to MySQL

I have question about triggers. I've created a function in plpgSQL for PostgreSQL, but, now I need to do this in MySQL. I tried almost everything and I don't know how to to this. Does anybody knows what to do and help me?

Here is the code in in plpgSQL:

CREATE OR REPLACE FUNCTION provjera_rezultata() RETURNS TRIGGER AS $$
    DECLARE
    neodigrane_utakmice INT;

    BEGIN
    SELECT COUNT(*) INTO neodigrane_utakmice
    FROM utakmice AS u
        JOIN momcadi AS m1
            ON m1.momcad_ID = u.momcad_a
        JOIN momcadi AS m2
            ON m2.momcad_ID = u.momcad_b
    WHERE rezultat IS NULL;

    IF neodigrane_utakmice > 0 THEN
        RAISE NOTICE 'Broj utakmica bez rezultata je %', neodigrane_utakmice;
    END IF;

    RETURN NEW;
    END;
$$ LANGUAGE plpgsql;

Also, I don't know what to use in MySQL for "RAISE NOTICE" from PostgreSQL.

I did this, but I get error :(

DELIMITER $$

CREATE TRIGGER provjera_rezultata
    AFTER INSERT ON utakmice FOR EACH ROW
    DECLARE broj INTEGER;

BEGIN
    SELECT COUNT(*) INTO broj
    FROM utakmice AS u
        JOIN momcadi AS m1
            ON m1.momcad_ID = u.momcad_a
        JOIN momcadi AS m2
            ON m2.momcad_ID = u.momcad_b
    WHERE rezultat IS NULL;

    IF broj > 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Postoje utakmice bez konacnog rezultata!';
    END IF;
END $$

Upvotes: 2

Views: 1204

Answers (2)

Kolonka
Kolonka

Reputation: 56

In MySQL you declare variables inside the begin-end. This code gives me no syntax error:

# test tables
CREATE TABLE utakmice(
    momcad_a INT,
    momcad_b INT,
    rezultat INT,
    PRIMARY KEY (momcad_a, momcad_b)
);
CREATE TABLE momcadi(
    momcad_ID INT AUTO_INCREMENT PRIMARY KEY
);


DELIMITER $$
CREATE TRIGGER provjera_rezultata
    AFTER INSERT ON utakmice FOR EACH ROW
BEGIN
    DECLARE broj INTEGER;
    SELECT COUNT(*) INTO broj
    FROM utakmice AS u
        JOIN momcadi AS m1
            ON m1.momcad_ID = u.momcad_a
        JOIN momcadi AS m2
            ON m2.momcad_ID = u.momcad_b
    WHERE rezultat IS NULL;

    IF broj > 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Postoje utakmice bez konacnog rezultata!';
    END IF;
END $$

I know this post is old but I somehow came here and it was missing an answer. For future problems this answer can be helpful.

Upvotes: 0

Andreas Wederbrand
Andreas Wederbrand

Reputation: 40021

The inner parts of your code could remain basically the same as it does.

Triggers are created with CREATE TRIGGER.

Notices in MySQL are called signals. You do 

SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'An error occurred';

Upvotes: 1

Related Questions