Trigger to populate attributes from another attribute in the same table-CodePudding

I would like to fill automatically by trigger from a value of the "nat_cond" attribute of the "depart_bt" table, the values of 3 other attributes of the same table "sect_ph", "sect_n", "metal_cond" per application of split_part(..,'_',..) function.

For instance : If value of the "nat_cond": 3*240 120mm2_AR2V_ALU

I should get:

"sect_ph" : 3*240 120mm2

"sect_n" : AR2V

"metal_cond" : ALU

My code is as follows:

CREATE OR REPLACE FUNCTION nat_cond_auto() RETURNS TRIGGER 
language plpgsql AS 
$$
BEGIN
    IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
        NEW.sect_ph:= (SELECT split_part(NEW.nat_cond::TEXT, '_', 1) LIMIT 1);
        NEW.sect_n:= (SELECT split_part(NEW.nat_cond::TEXT, '_', 2) FROM depart_bt LIMIT 1);
        NEW.metal_cond:= (SELECT split_part(NEW.nat_cond::TEXT, '_', 3) FROM depart_bt LIMIT 1);
    END IF;
    RETURN NEW;
END; 
$$
;

-- table depart_bt
DROP TRIGGER IF EXISTS nat_conducteur ON depart_bt;
CREATE TRIGGER nat_conducteur BEFORE INSERT OR UPDATE ON depart_bt
FOR EACH ROW EXECUTE PROCEDURE nat_cond_auto();

However, the trigger works well except for the first added row which does nothing.

I think I looped something. Thank you in advance for your help.

CodePudding user response：

Your SELECT statements in the assignment are completely useless as you don't use any data from the potential rows that would be returned. But those selects are also the reason your trigger doesn't work: if the table is empty select ... from depart_bt limit 1 won't return anything.

I think you simply want:

CREATE OR REPLACE FUNCTION nat_cond_auto() RETURNS TRIGGER 
language plpgsql AS 
$$
BEGIN
    IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
        NEW.sect_ph := split_part(NEW.nat_cond::TEXT, '_', 1);
        NEW.sect_n := split_part(NEW.nat_cond::TEXT, '_', 2);
        NEW.metal_cond := split_part(NEW.nat_cond::TEXT, '_', 3);
    END IF;
    RETURN NEW;
END; 
$$
;

Note that the IF TG_OP = ... isn't really necessary, because your trigger definition makes the trigger only fire for update or insert statements.