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)

Note

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

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_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

Warning

to be completed

ft_valve_pipe_update

Warning

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...).

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_qwat_node_delete

ft_vw_qwat_node_insert

ft_vw_qwat_node_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

Functions

fn_get_district

Returns the id of the first overlapping district.

Params:
  • geom

Perform an intersection between the geom and district.

fn_get_pressurezone

Returns the id of the first overlapping pressurezone.

Params:
  • geom

Perform an intersection between the geom and the pressurezone geometry.

fn_get_printmap_id

Returns the id of the first overlapping printmap.

Params:
  • 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.

Params:
  • 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.

Params:
  • 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.

Params:
  • _point (geometry)
  • deactivate_node_add_pipe_vertex
Behaviour:
  • 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.

Params:
  • 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).

Params:
  • _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).

Params:
  • _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 ?

Warning

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.

Params:
  • geometry

Perform an intersection between the geometry and the pipe geometries.

fn_pipe_update_valve

Update pipe valves informations.

Params:
  • _pipe_id

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

fn_update_pipe_crossing

Params:
  • update_existing
  • delete_unused
Process:
  • 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

Warning

This trigger use a fixed fk_status values = 1301

fn_update_sequences

Update a specific sequence.

Params:
  • 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.

Tables

consumptionzone

No triggers

cover

crossing

folder

  • No triggers

leak

meter_reference

  • No triggers

network_element

  • No triggers

node

part

  • No triggers

pipe

pressurecontrol

pressurezone

remote

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

valve

worker

  • No triggers

Views

vw_consumptionzone

  • No triggers

vw_element_meter

vw_element_part

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_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 - DELETE / UPDATE pipe
  • RULE - UPDATE / UPDATE pipe

vw_printmap

  • No triggers

vw_protectionzone

  • No triggers

vw_remote

  • No triggers

vw_search_view

  • No triggers

vw_subscriber_pipe_relation

  • No triggers

vw_valves_lines

  • No triggers