-- Collection of code snippets by Arne Vajhøj 
-- posted to eksperten.dk, usenet and other places (2002-now) 
CREATE TABLE fun (
    id INTEGER NOT NULL,
    val INTEGER,
    PRIMARY KEY(id)
);

DELIMITER //

CREATE TRIGGER duplcheck
BEFORE INSERT
ON fun
FOR EACH ROW BEGIN
    DECLARE n INT;
    SET n = (SELECT COUNT(*) FROM fun WHERE val = new.val);
    IF(n > 0) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Ooops - Houston we have a problem!';
    END IF;
END//

DELIMITER ;

INSERT INTO fun VALUES(1, 1);
INSERT INTO fun VALUES(2, 2);
INSERT INTO fun VALUES(3, 2);

DROP TABLE fun;
