CODEWITHSUNDEEP
postgresqltriggersplpgsqlpostgresql-8.4procedures
KGs
KGs

Reputation: 923

PostgreSQL Trigger Exception

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?

Upvotes: 0

Views: 2847

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656521

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:

Upvotes: 3

Related Questions