Meaning: a trigger that if an entry appears in the superuser.structurejunction table with assertgroup = 122, then create a new entry in another table.

Trigger Script:

REATE OR REPLACE FUNCTION create_django_pole() RETURNS TRIGGER AS $$ DECLARE var_objid INTEGER; BEGIN IF (TG_OP = 'INSERT') THEN var_objid = NEW.objectid; IF (NEW.gdb_is_delete = 1) THEN DELETE FROM PUBLIC.un_electric_pole WHERE structure_junction_id = var_objid; RETURN NEW; ELSIF (NEW.gdb_is_delete = 0) THEN IF NOT EXISTS(SELECT 1 FROM PUBLIC.un_electric_pole WHERE structure_junction_id = var_objid) THEN INSERT INTO PUBLIC.un_electric_pole (structure_junction_id) VALUES (var_objid); RETURN NEW; END IF; RETURN NEW; END IF; END IF; END; $$ LANGUAGE plpgsql; ALTER FUNCTION create_django_pole() OWNER TO superuser; 

The trigger itself:

 CREATE TRIGGER create_arcgis_pole AFTER INSERT ON superuser.structurejunction FOR EACH ROW WHEN (NEW.assetgroup = 122) EXECUTE PROCEDURE create_django_pole(); 

Problem: when adding a record to the superuser.structurejunction table, the trigger is invoked, but when you try to create an entry in public.un_electric_pole, an error occurs that violates the non null structure_junction_id field limit (var_objid (and the value is shown directly, for example, 34)). And the records in the first and second tables are not added. If I turn off the trigger (delete, instead of with the function), then in the superuser.structurejunction, the data is normally recorded normally.

The trigger should be triggered after adding a record, but it seems that the record has not yet added.

gdb_is_delete - field indicating whether the record was deleted. here it is not important and do not pay attention.

DB - postgresql

  • The trigger is correct (unless it is not competitive-safe because of the race condition between exists and insert - use the insert on conflict). Do you have on public.un_electric_pole other triggers? Add table definitions to have a minimal reproducible example. - Fine
  • You want to say that something like this is written in your "such a" not null constraint is broken when inserting the value 34 "? - Andrew Bystrov 2:46 pm

0