CODEWITHSUNDEEP
postgresqltriggersplpgsql
Matthew Setter
Matthew Setter

Reputation: 2447

Postgresql before update/insert trigger doesn't appear to work

I have the following trigger function: 

CREATE OR REPLACE FUNCTION update_modelname_function()
  RETURNS trigger AS
$BODY$
BEGIN
  IF tg_op = 'INSERT' THEN
     new.model_name := upper(new.model_name);
     RETURN new;
  END IF;
  IF tg_op = 'UPDATE' THEN
     old.model_name := upper(old.model_name);
     RETURN new;
  END IF;
END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

What I'm trying to achieve is for the value of the column model_name to always be uppercased when it's persisted in the table. However nothing seems to happen. Any ideas?

Upvotes: 1

Views: 3430

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656291

You accidentally updated OLD instead of NEW. Try:

CREATE OR REPLACE FUNCTION update_modelname_function()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   IF TG_OP = 'INSERT' THEN
      NEW.model_name := upper(NEW.model_name);
      RETURN NEW;
   ELSIF TG_OP = 'UPDATE' THEN
      NEW.model_name := upper(NEW.model_name);  -- !
      RETURN NEW;
   END IF;
END
$func$;

If the example shows the whole code, and the actual trigger(s) only fires on INSERT and/or UPDATE, further simplify:

CREATE OR REPLACE FUNCTION update_modelname_function()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   NEW.model_name := upper(NEW.model_name);
   RETURN NEW;
END
$func$;

Upvotes: 5

Related Questions