4.2. Triggers

Most of the triggers are located in the qwat_od schema.

We can classify the triggers in 3 categories:

  1. specific behaviour triggers
  2. audit triggers
  3. redirection triggers (triggers on view)


In the description below, triggers’s name which are not prefixed with the schema name are contained in qwat_od schema.

4.2.1. Behaviour Trigger functions ft_geom3d_altitude

  • UPDATE altitude
  • UPDATE geometry

Altitude is prioritary on Z value of the geometry (if both changed, only altitude is taken into account) ft_controled_crossing

  • UPDATE controled = True ft_leak_pipe ft_leak_repaired

  • UPDATE _repaired ft_node_add_pipe_vertex

Add a vertex to the corresponding pipe if it intersects. When the node is close enough to the pipe (< 1 micrometer) the node is considered to intersect the pipe It allows to deal with intersections that cannot be represented by floating point numbers

  • UPDATE pipe (geometry) using St_Snap ft_geometry_alternative_aux

  • Update table fields ft_geometry_alternative_main

  • Update table fields ft_node_geom ft_pipe_node_moved

Update pipe’s nodes extremities by creating new nodes, or taking existing ones. ft_pipe_geom ft_pipe_node_type ft_pipe_tunnelbridge


to be completed ft_valve_pipe_update


to be completed ft_tank ft_valve_update ft_valve_handle_altitude

  • Update table fields ft_valve_node_set_type qwat_sys.if_modified_func

  • Audit trigger

Add a new record in qwat_sys.logged_actions (schema, table, user, time…).

4.2.2. Redirection trigger functions ft_element_hydrant_delete ft_element_hydrant_insert ft_element_hydrant_update ft_element_installation_delete ft_element_installation_insert ft_element_installation_update ft_element_meter_delete ft_element_meter_insert ft_element_meter_update ft_element_part_delete ft_element_part_insert ft_element_part_update ft_element_samplingpoint_delete ft_element_samplingpoint_insert ft_element_samplingpoint_update ft_element_subscriber_delete ft_element_subscriber_insert ft_element_subscriber_update ft_element_valve_delete ft_element_valve_insert ft_element_valve_update ft_installation_chamber_delete ft_installation_chamber_insert ft_installation_chamber_update ft_installation_pressurecontrol_delete ft_installation_pressurecontrol_insert ft_installation_pressurecontrol_update ft_installation_pump_delete ft_installation_pump_insert ft_installation_pump_update ft_installation_source_delete ft_installation_source_insert ft_installation_source_update ft_installation_tank_delete ft_installation_tank_insert ft_installation_tank_update ft_installation_treatment_delete ft_installation_treatment_insert ft_installation_treatment_update ft_node_element_delete ft_node_element_insert ft_node_element_update ft_vw_all_nodes_delete ft_vw_all_nodes_insert ft_vw_all_nodes_update ft_vw_qwat_installation_delete ft_vw_qwat_installation_insert ft_vw_qwat_installation_update ft_vw_qwat_network_element_delete ft_vw_qwat_network_element_insert ft_vw_qwat_network_element_update

4.2.3. Functions fn_get_district

Returns the id of the first overlapping district.

  • geom

Perform an intersection between the geom and district. fn_get_pressurezone

Returns the id of the first overlapping pressurezone.

  • geom

Perform an intersection between the geom and the pressurezone geometry. fn_get_printmap_id

Returns the id of the first overlapping printmap.

  • geom

Perform an intersection between the geom and printmap geometry. fn_get_printmaps

Returns a string contaning all the short names of the polygons in table printmap which overlap the given geometry.

  • geom
  • result

Perform an intersection between the geom and the printmap geometry. fn_litres_per_cm

Calculate the litres_per_cm of a tank cistern.

  • fk_type
  • dim1
  • dim2

Perform a calculation with dim1 & dim2. fn_node_create

Returns the node for a given geometry (point). If node does not exist, create it.

  • _point (geometry)
  • deactivate_node_add_pipe_vertex
  • Search for a node a the _point location.
  • If a node if found
    • Deactivate the node_add_pipe_vertex_insert trigger if needed
    • INSERT into node
    • Reactivate the node_add_pipe_vertex_insert trigger if needed fn_node_get_ids

Returns a list of node IDs contained a given extent.

  • extent

Perform a selection on node with the given extent. If no extent is provided, return all ids. fn_node_set_type[]

Set the type and orientation for node. If three pipe arrives at the node: intersection. If one pipe: end. If two: depends on characteristics of pipe: year (is different), material (and year), diameter(and material/year).

  • _node_ids[]

Perform fn_node_set_type for each node given in param. If no ids are given, the process is perform on all node ids. fn_node_set_type

Set the orientation and type for a node. If three pipe arrives at the node: intersection. If one pipe: end. If two: depends on characteristics of pipe: year (is different), material (and year), diameter(and material/year).

  • _node_id
Perform a lot of processing:
  • Count the active pipes associated to this node (by joining tables from qwat_vlstatus & qwat_vl_pipe_function)
  • If count = 0:
    • If _node_id not on a pipe extremity (fk_node_a, fk_node_b)
      • If this is really a node, delete it from node
      • Else, the node must be on the pipe vertex
        • Get geometry of the pipe
        • If the geometry is null: raise an error
        • Else calculate the orientation of the pipe
  • If count = 1 or 2
    • Loop over them, and take the 2 first/last vertices of the pipe to determine orientation (used for symbology)
  • If count > 2
    • Nothing to do
  • UPDATE node (_pipe_node_type, _pipe_orientation, _pipe_schema_visible) fn_node_update_id

Not used anymore ?


to be completed fn_pipe_get_id

Returns the pipe at a given position. If geometry is a point, do not return a pipe which ends on it.

  • geometry

Perform an intersection between the geometry and the pipe geometries. fn_pipe_update_valve

Update pipe valves informations.

  • _pipe_id

Perform an update on pipe (_valve_count, _valve_closed) by joining valve. fn_update_pipe_crossing

  • update_existing
  • delete_unused
  • Get all crossing points (by performing intersections between pipes)
  • For each point
    • If point is at the end of a pipe: do nothing
    • Else, for the 2 crossing pipes :
      • Perform azimuth for all segments of the pipes with the corresponding linear referencing
      • Find the correct segment according to linear referencing of the crossing point
      • Get the corresponding azimuth according to segment id
      • Reports errors if azimuth are null
      • If the crossing is already existing: update it (_pipe1_id, _pipe1_angle, _pipe2_id, _pipe2_angle,geometry)
      • Else: create it
    • Delete the old crossing


This trigger use a fixed fk_status values = 1301 fn_update_sequences

Update a specific sequence.

  • none

For all columns in the DB associated to a sequence, calculate the max value + 1 for the column, and reassociate the next value of the sequence to that max.

4.2.4. Tables chamber consumptionzone

No triggers cover crossing distributor district folder

  • No triggers hydrant installation leak meter meter_reference

  • No triggers network_element

  • No triggers node part

  • No triggers pipe pressurecontrol pressurezone printmap protectionzone remote samplingpoint source subscriber subscriber_reference surveypoint tank

  • qwat_sys.if_modified_func
  • BEFORE INSERT OR UPDATE cistern1_fk_type, cistern1_dimension_1, cistern1_dimension_2, cistern2_fk_type, cistern2_dimension_1, cistern2_dimension_2 - ft_tank treatment valve worker

  • No triggers

4.2.5. Views vw_element_hydrant vw_consumptionzone

  • No triggers vw_element_installation vw_element_meter vw_element_part vw_element_samplingpoint vw_element_subscriber vw_export_hydrant

  • No triggers vw_export_installation

  • No triggers vw_export_meter

  • No triggers vw_export_part

  • No triggers vw_export_subscriber

  • No triggers vw_export_valve

  • No triggers vw_element_valve vw_installation_chamber vw_installation_pump vw_installation_source vw_installation_tank vw_installation_treatment vw_leak

  • No triggers vw_node_element vw_pipe

  • No triggers vw_pipe_child_parent

  • No triggers vw_pipe_schema

  • RULE - UPDATE pipe vw_pipe_schema_error

  • No triggers vw_pipe_schema_merged

  • No triggers vw_pipe_schema_visibleitems

  • RULE - UPDATE / UPDATE pipe vw_printmap

  • No triggers vw_protectionzone

  • No triggers vw_qwat_installation vw_all_nodes vw_remote

  • No triggers vw_search_view

  • No triggers vw_subscriber_pipe_relation

  • No triggers vw_valves_lines

  • No triggers