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

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?

CodePudding user response：

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;