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