Criar um trigger para impedir entrada de dados inválidos

[softklin]

Olá. Venho aqui pedir ajuda num assunto que nunca tinha usado antes, que são os triggers. Habitualmente, fazia toda esta verificação com uma linguagem de programação antes de enviar os dados propriamente ditos, mas desta vez gostava de usar esta forma.

Tenho uma tabela com nomes de pastas, em que cada um tem um campo a dizer quem é o seu pai, isto é, a pasta imediatamente acima. Algo assim:

ns_id, ns_slug, ns_parent

1, superior, NULL

2, medio, 1

3, outra_pasta, NULL

4, inferior, 2

Antes da inserção, queria verificar se já existia uma pasta com o mesmo nome, no mesmo nível, isto é, verificar se existe algum registo com o mesmo pai e nome. Estive a pesquisar, e dei de caras com um artigo do StackOverflow que tem um hack para isto:

http://stackoverflow.com/a/229802/515814

Tentei assim, mas o MySQL Workbench está a dar-me erros de sintaxe

CREATE TRIGGER avoid_same_namespace_slug
BEFORE INSERT ON namespaces
FOR EACH ROW
BEGIN
    IF (SELECT COUNT(*) FROM namespaces WHERE ns_slug = NEW.ns_slug AND ns_parent = NEW.ns_parent) > 0 THEN
        DECLARE dummy INT;

        SELECT Your meaningful error message goes here INTO dummy 
        FROM mytable
        WHERE mytable.id=new.id
    END IF
END;

Afinal, como se define um trigger para esta situação? Ou tenho de fazer a validação na camada de aplicação?

[José Lopes]

O teu problema está no segundo select..porque aquilo nao é uma instrução válida...

Experimenta isto:

CREATE TRIGGER avoid_same_namespace_slug
BEFORE INSERT ON namespaces
FOR EACH ROW
BEGIN
    IF (SELECT COUNT(*) FROM namespaces WHERE ns_slug = NEW.ns_slug AND ns_parent = NEW.ns_parent) > 0 THEN
       SET NEW='Error:A tua mensagem de erro.';
    END IF;
END;

Podes ainda experimentar qualquer coisa do género usando o modo que tens:

SELECT 'A tua mensagem'  INTO dummy  from tua_tabela where 1 = 0;

PS - Não conheço muito bem em MYSQL onde entram os ";"... por isso verifica os ";"

[softklin]

Obrigado pela ajuda. Erros de sintaxe já não tenho. A definição do trigger está assim:

DELIMITER $$

CREATE TRIGGER avoid_same_namespace_slug
BEFORE INSERT ON namespaces
FOR EACH ROW
BEGIN
    IF (SELECT COUNT(*) FROM namespaces WHERE ns_slug = NEW.ns_slug AND ns_parent = NEW.ns_parent) > 0 THEN
       SET NEW='Error:Namespace já existe';
       # apenas para teste
       INSERT INTO namespaces VALUES (NULL, 'ERRO', 999);
    END IF;
END$$

O problema é que inserindo um registo com os mesmos dados, ele nunca entra na condição. Por exemplo, inseri, sem qualquer erro ou aviso, os seguintes dados, por ordem:

ns_id, ns_slug,ns_parent

1, topo, NULL

2, medio, 1

3, topo, NULL

4, teste, 2

O problema está no registo 3, que foi inserido, quando já existe o 1, com os mesmos dados. Não posso usar o UNIQUE porque pode haver nomes de pastas iguais, mas a níveis diferentes. Alguma sugestão?

No caso de não conseguir usar um trigger, é correto eliminar o ns_id (PK) e passar para PK o par (ns_slug, ns_parent) ? Caso contrário, aplico a restrição na camada de aplicação. Não me queria chatear muito com isto, apenas achei que era uma boa oportunidade de ver os triggers em ação.

[José Lopes]

Criares uma unique key constituída pelo par das duas colunas podes ser uma solução.

Não sei se o MYSQL Suporta, mas eu em oracle faço isso frequentemente.

De qualquer modo é estranho porque a instrução parece-me bem...

De qualquer modo podes dar uma vista de olhos neste post, não perdes nada...

http://rpbouman.blogspot.pt/2009/12/validating-mysql-data-entry-with_15.html

[softklin]

Por acaso passei por esse post ontem, e de facto, não queria complicar, até porque nem percebo nada de sinais. Estive a ver se o MySQL suporta pares de campos únicos, e sim, suporta!

http://blog.gordaen.com/2009/07/08/mysql-unique-key-pairs/

Mas neste caso, tenho de fazer uma alteração à tabela. Ao definir como único o par (ns_slug, ns_parent), se inserir esta sequência de valores

ns_id, ns_slug, ns_parent

1, topo, NULL

2, meio, 1

3, topo, NULL

ele insere normalmente, porque aparentemente não considera como igual o facto de o campo ns_parent ter em ambos os registos o valor NULL. O que optei por fazer foi mudar o campo ns_parent para NOT NULL, e definir um valor fora do range do AUTO_INCREMENT, tal como o 0, para definir os níveis de topo.

Após muitos DROP e CREATE, a estrutura da tabela ficou:

CREATE TABLE namespaces(
    ns_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    ns_slug varchar(255) NOT NULL,
    ns_parent INT NOT NULL,
    UNIQUE KEY ns_unique (ns_slug, ns_parent)
)

sem recurso a triggers ou stored procedures, usando simplesmente o par único (ns_slug, ns_parent) que deve ser suficiente para o meu caso.

Muito obrigado pela tua ajuda, Jose Lopes! 😕