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)

Nota

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

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

4.2.1.2. ft_controled_crossing

  • UPDATE controled = True

4.2.1.3. ft_leak_pipe

4.2.1.4. ft_leak_repaired

  • UPDATE _repaired

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

4.2.1.6. ft_geometry_alternative_aux

  • Update table fields

4.2.1.7. ft_geometry_alternative_main

  • Update table fields

4.2.1.8. ft_node_geom

4.2.1.9. ft_pipe_node_moved

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

4.2.1.10. ft_pipe_geom

4.2.1.11. ft_pipe_node_type

4.2.1.12. ft_pipe_tunnelbridge

Avvertimento

to be completed

4.2.1.13. ft_valve_pipe_update

Avvertimento

to be completed

4.2.1.14. ft_tank

4.2.1.15. ft_valve_update

4.2.1.16. ft_valve_handle_altitude

  • Update table fields

4.2.1.17. ft_valve_node_set_type

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

4.2.2.1. ft_element_hydrant_delete

4.2.2.2. ft_element_hydrant_insert

4.2.2.3. ft_element_hydrant_update

4.2.2.4. ft_element_installation_delete

4.2.2.5. ft_element_installation_insert

4.2.2.6. ft_element_installation_update

4.2.2.7. ft_element_meter_delete

4.2.2.8. ft_element_meter_insert

4.2.2.9. ft_element_meter_update

4.2.2.10. ft_element_part_delete

4.2.2.11. ft_element_part_insert

4.2.2.12. ft_element_part_update

4.2.2.13. ft_element_samplingpoint_delete

4.2.2.14. ft_element_samplingpoint_insert

4.2.2.15. ft_element_samplingpoint_update

4.2.2.16. ft_element_subscriber_delete

4.2.2.17. ft_element_subscriber_insert

4.2.2.18. ft_element_subscriber_update

4.2.2.19. ft_element_valve_delete

4.2.2.20. ft_element_valve_insert

4.2.2.21. ft_element_valve_update

4.2.2.22. ft_installation_chamber_delete

4.2.2.23. ft_installation_chamber_insert

4.2.2.24. ft_installation_chamber_update

4.2.2.25. ft_installation_pressurecontrol_delete

4.2.2.26. ft_installation_pressurecontrol_insert

4.2.2.27. ft_installation_pressurecontrol_update

4.2.2.28. ft_installation_pump_delete

4.2.2.29. ft_installation_pump_insert

4.2.2.30. ft_installation_pump_update

4.2.2.31. ft_installation_source_delete

4.2.2.32. ft_installation_source_insert

4.2.2.33. ft_installation_source_update

4.2.2.34. ft_installation_tank_delete

4.2.2.35. ft_installation_tank_insert

4.2.2.36. ft_installation_tank_update

4.2.2.37. ft_installation_treatment_delete

4.2.2.38. ft_installation_treatment_insert

4.2.2.39. ft_installation_treatment_update

4.2.2.40. ft_node_element_delete

4.2.2.41. ft_node_element_insert

4.2.2.42. ft_node_element_update

4.2.2.43. ft_vw_all_nodes_delete

4.2.2.44. ft_vw_all_nodes_insert

4.2.2.45. ft_vw_all_nodes_update

4.2.2.46. ft_vw_qwat_installation_delete

4.2.2.47. ft_vw_qwat_installation_insert

4.2.2.48. ft_vw_qwat_installation_update

4.2.2.49. ft_vw_qwat_network_element_delete

4.2.2.50. ft_vw_qwat_network_element_insert

4.2.2.51. ft_vw_qwat_network_element_update

4.2.3. Functions

4.2.3.1. fn_get_district

Returns the id of the first overlapping district.

Params:

  • geom

Perform an intersection between the geom and district.

4.2.3.2. fn_get_pressurezone

Returns the id of the first overlapping pressurezone.

Params:

  • geom

Perform an intersection between the geom and the pressurezone geometry.

4.2.3.3. fn_get_printmap_id

Returns the id of the first overlapping printmap.

Params:

  • geom

Perform an intersection between the geom and printmap geometry.

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

4.2.3.5. fn_litres_per_cm

Calculate the litres_per_cm of a tank cistern.

Params:

  • fk_type

  • dim1

  • dim2

Perform a calculation with dim1 & dim2.

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

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

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

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

4.2.3.10. fn_node_update_id

Not used anymore ?

Avvertimento

to be completed

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

4.2.3.12. fn_pipe_update_valve

Update pipe valves informations.

Params:

  • _pipe_id

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

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

Avvertimento

This trigger use a fixed fk_status values = 1301

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

4.2.4. Tables

4.2.4.1. chamber

4.2.4.2. consumptionzone

No triggers

4.2.4.3. cover

4.2.4.4. crossing

4.2.4.5. distributor

4.2.4.6. district

4.2.4.7. folder

  • No triggers

4.2.4.8. hydrant

4.2.4.9. installation

4.2.4.10. leak

4.2.4.11. meter

4.2.4.12. meter_reference

  • No triggers

4.2.4.13. network_element

  • No triggers

4.2.4.14. node

4.2.4.15. part

  • No triggers

4.2.4.16. pipe

4.2.4.17. pressurecontrol

4.2.4.18. pressurezone

4.2.4.19. printmap

4.2.4.20. protectionzone

4.2.4.21. pump

4.2.4.22. remote

4.2.4.23. samplingpoint

4.2.4.24. source

4.2.4.25. subscriber

4.2.4.26. subscriber_reference

4.2.4.27. surveypoint

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

4.2.4.29. treatment

4.2.4.30. valve

4.2.4.31. worker

  • No triggers

4.2.5. Views

4.2.5.1. vw_element_hydrant

4.2.5.2. vw_consumptionzone

  • No triggers

4.2.5.3. vw_element_installation

4.2.5.4. vw_element_meter

4.2.5.5. vw_element_part

4.2.5.6. vw_element_samplingpoint

4.2.5.7. vw_element_subscriber

4.2.5.8. vw_export_hydrant

  • No triggers

4.2.5.9. vw_export_installation

  • No triggers

4.2.5.10. vw_export_meter

  • No triggers

4.2.5.11. vw_export_part

  • No triggers

4.2.5.12. vw_export_subscriber

  • No triggers

4.2.5.13. vw_export_valve

  • No triggers

4.2.5.14. vw_element_valve

4.2.5.15. vw_installation_chamber

4.2.5.16. vw_installation_pressurecontrol

4.2.5.17. vw_installation_pump

4.2.5.18. vw_installation_source

4.2.5.19. vw_installation_tank

4.2.5.20. vw_installation_treatment

4.2.5.21. vw_leak

  • No triggers

4.2.5.22. vw_node_element

4.2.5.23. vw_pipe

  • No triggers

4.2.5.24. vw_pipe_child_parent

  • No triggers

4.2.5.25. vw_pipe_schema

  • RULE - UPDATE pipe

4.2.5.26. vw_pipe_schema_error

  • No triggers

4.2.5.27. vw_pipe_schema_merged

  • No triggers

4.2.5.28. vw_pipe_schema_visibleitems

  • RULE - DELETE / UPDATE pipe

  • RULE - UPDATE / UPDATE pipe

4.2.5.29. vw_printmap

  • No triggers

4.2.5.30. vw_protectionzone

  • No triggers

4.2.5.31. vw_qwat_installation

4.2.5.32. vw_qwat_network_element

4.2.5.33. vw_all_nodes

4.2.5.34. vw_remote

  • No triggers

4.2.5.35. vw_search_view

  • No triggers

4.2.5.36. vw_subscriber_pipe_relation

  • No triggers

4.2.5.37. vw_valves_lines

  • No triggers