Trigger doesn't work and doesn't show any errors

Question

I'm trying to create a trigger that will shorten the name and the middle name to initials. That's what i have:

CREATE OR REPLACE FUNCTION myfunc() RETURNS TRIGGER AS $$
DECLARE nm VARCHAR(50);
DECLARE mdnm VARCHAR(50);
BEGIN
   nm = LEFT(NEW.name, 1);
   mdnm = LEFT(NEW.middle_name, 1);
   SET NEW.name = nm;
   SET NEW.middle_name = mdnm;
   RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER table_before_insert BEFORE INSERT OR UPDATE ON table1
FOR EACH ROW EXECUTE PROCEDURE myfunc();

But for some reasons it doesn't work, what can it be?

user330315 · Accepted Answer · 2021-12-19 12:18:27Z

3

As documented in the manual variables are assigned with := or =. But not with the SET command - which changes configuration properties. You also don't need a separate DECLARE block for each variable:

So your trigger function should look like this:

CREATE OR REPLACE FUNCTION myfunc() RETURNS TRIGGER AS $$
DECLARE 
   nm VARCHAR(50);
   mdnm VARCHAR(50);
BEGIN
   nm := LEFT(NEW.name, 1);
   mdnm := LEFT(NEW.middle_name, 1);
   NEW.name := nm;
   NEW.middle_name := mdnm;
   RETURN NEW;
END;
$$ LANGUAGE plpgsql;

You don't even need the variables:

CREATE OR REPLACE FUNCTION myfunc() RETURNS TRIGGER AS $$
BEGIN
   NEW.name := LEFT(NEW.name, 1);
   NEW.middle_name := LEFT(NEW.middle_name, 1);
   RETURN NEW;
END;
$$ LANGUAGE plpgsql;

answered Dec 19, 2021 at 12:18

user330315

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

Collectives™ on Stack Overflow

Trigger doesn't work and doesn't show any errors

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related