0

I'm having problems with creating this trigger in PostgreSQL 8.4.

CREATE OR REPLACE FUNCTION tbi_Usuarios() RETURNS TRIGGER AS $tbi_Usuarios$
    BEGIN
        IF trim(both ' ' from NEW.Nombre_usuario) = '' OR NEW.Nombre_usuario IS NULL THEN
            RAISE EXCEPTION 'Debes ingresar un nombre de usuario.';
        END IF;

    IF NEW.Password = '' OR NEW.Password IS NULL THEN
        RAISE EXCEPTION 'Debes ingresar una contraseña correctamente';
    ELSE
        NEW.Password := md5(NEW.Password);
    END IF;

    IF Fecha_registro IS NULL THEN
        NEW.Fecha_registro := current_timestamp;
    END IF;

    RETURN NEW;
END;
$tbi_Usuarios$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS tr_tbi_Usuarios ON "Usuarios";
CREATE TRIGGER tr_tbi_Usuarios BEFORE INSERT ON "Usuarios"
FOR EACH ROW EXECUTE PROCEDURE tbi_Usuarios();

The thing is that, when I try to insert a row in the database, the following error shows up:

"el registro << new >> no tiene un campo << nombre_usuario >>"

or in English:

"the table << new >> doesn't have a column << nombre_usuario >>"

But in my database, I'm REALLY sure that the columns Nombre_usuario, Password, Fecha_registro exist!

Can anyone help me please?

1 Answer 1

3

You most probably tripped over upper case names. I am not getting tired of advising not to use those.

You probably have a column named

"Nombre_usuario"

enclosed in double-quotes ("") which preserve the mixed case spelling.
But in your trigger function you write:

NEW.Nombre_usuario

without double quotes. Unquoted identifiers are converted to lower case. So this is the same as:

NEW.nombre_usuario

but not:

NEW."Nombre_usuario"

Always double-quote mixed case identifiers. Or (much better) exclusively use lower-case identifiers to begin with and save yourself the trouble.
(The identifier for the NEW row in a trigger function is never quoted.)

Further reading:

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.