4.2. Triggers¶
Most of the triggers are located in the qwat_od schema.
We can classify the triggers in 3 categories:
specific behaviour triggers
audit triggers
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.
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¶
UPDATE fk_pipe = fn_pipe_get_id
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¶
Update table fields
4.2.1.9. ft_pipe_node_moved¶
Update pipe’s nodes extremities by creating new nodes, or taking existing ones.
UPDATE pipe (fk_node_a, fk_node_b) using fn_node_create
4.2.1.10. ft_pipe_geom¶
Update table fields
4.2.1.11. ft_pipe_node_type¶
Update table fields
4.2.1.12. ft_pipe_tunnelbridge¶
Warning
to be completed
4.2.1.13. ft_valve_pipe_update¶
Warning
to be completed
4.2.1.14. ft_tank¶
Update table fields
4.2.1.15. ft_valve_update¶
UPDATE pipe
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¶
DELETE hydrant
DELETE vw_node_element
4.2.2.2. ft_element_hydrant_insert¶
INSERT vw_node_element
INSERT hydrant
4.2.2.3. ft_element_hydrant_update¶
UPDATE vw_node_element
UPDATE hydrant
4.2.2.4. ft_element_installation_delete¶
DELETE vw_qwat_installation
DELETE vw_node_element
4.2.2.5. ft_element_installation_insert¶
INSERT vw_node_element
INSERT vw_qwat_installation
4.2.2.6. ft_element_installation_update¶
UPDATE vw_node_element
UPDATE vw_qwat_installation
4.2.2.7. ft_element_meter_delete¶
DELETE meter
DELETE vw_node_element
4.2.2.8. ft_element_meter_insert¶
INSERT vw_node_element
INSERT meter
4.2.2.9. ft_element_meter_update¶
UPDATE vw_node_element
UPDATE meter
4.2.2.10. ft_element_part_delete¶
DELETE part
DELETE vw_node_element
4.2.2.11. ft_element_part_insert¶
INSERT vw_node_element
INSERT part
4.2.2.12. ft_element_part_update¶
UPDATE vw_node_element
UPDATE part
4.2.2.13. ft_element_samplingpoint_delete¶
DELETE samplingpoint
DELETE vw_node_element
4.2.2.14. ft_element_samplingpoint_insert¶
INSERT vw_node_element
INSERT samplingpoint
4.2.2.15. ft_element_samplingpoint_update¶
UPDATE vw_node_element
4.2.2.16. ft_element_subscriber_delete¶
DELETE subscriber
DELETE vw_node_element
4.2.2.17. ft_element_subscriber_insert¶
INSERT vw_node_element
INSERT subscriber
4.2.2.18. ft_element_subscriber_update¶
UPDATE vw_node_element
4.2.2.19. ft_element_valve_delete¶
DELETE valve
DELETE vw_node_element
4.2.2.20. ft_element_valve_insert¶
INSERT vw_node_element
INSERT valve
4.2.2.21. ft_element_valve_update¶
UPDATE vw_node_element
UPDATE valve
4.2.2.22. ft_installation_chamber_delete¶
DELETE chamber
DELETE installation
4.2.2.23. ft_installation_chamber_insert¶
INSERT installation
INSERT chamber
4.2.2.24. ft_installation_chamber_update¶
UPDATE installation
UPDATE chamber
4.2.2.25. ft_installation_pressurecontrol_delete¶
DELETE pressurecontrol
DELETE installation
4.2.2.26. ft_installation_pressurecontrol_insert¶
INSERT installation
INSERT pressurecontrol
4.2.2.27. ft_installation_pressurecontrol_update¶
UPDATE installation
UPDATE pressurecontrol
4.2.2.28. ft_installation_pump_delete¶
DELETE pump
DELETE installation
4.2.2.29. ft_installation_pump_insert¶
INSERT installation
INSERT pump
4.2.2.30. ft_installation_pump_update¶
UPDATE installation
UPDATE pump
4.2.2.31. ft_installation_source_delete¶
DELETE source
DELETE installation
4.2.2.32. ft_installation_source_insert¶
INSERT installation
INSERT source
4.2.2.33. ft_installation_source_update¶
UPDATE installation
UPDATE source
4.2.2.34. ft_installation_tank_delete¶
DELETE tank
DELETE installation
4.2.2.35. ft_installation_tank_insert¶
INSERT installation
INSERT tank
4.2.2.36. ft_installation_tank_update¶
UPDATE installation
UPDATE tank
4.2.2.37. ft_installation_treatment_delete¶
DELETE treatment
DELETE installation
4.2.2.38. ft_installation_treatment_insert¶
INSERT installation
INSERT treatment
4.2.2.39. ft_installation_treatment_update¶
UPDATE installation
UPDATE treatment
4.2.2.40. ft_node_element_delete¶
DELETE network_element
4.2.2.41. ft_node_element_insert¶
Update table fields
INSERT network_element
4.2.2.42. ft_node_element_update¶
UPDATE node
UPDATE network_element
4.2.2.43. ft_vw_all_nodes_delete¶
DELETE network_element
4.2.2.44. ft_vw_all_nodes_insert¶
Update table fields
UPDATE node
UPDATE network_element
4.2.2.45. ft_vw_all_nodes_update¶
Update table fields
UPDATE node
UPDATE network_element
4.2.2.46. ft_vw_qwat_installation_delete¶
DELETE source
DELETE pump
DELETE tank
DELETE treatment
DELETE chamber
DELETE pressurecontrol
DELETE installation
4.2.2.47. ft_vw_qwat_installation_insert¶
INSERT installation
INSERT source
INSERT pump
INSERT tank
INSERT treatment
INSERT chamber
INSERT pressurecontrol
4.2.2.48. ft_vw_qwat_installation_update¶
UPDATE installation
UPDATE source
UPDATE pump
UPDATE tank
UPDATE treatment
UPDATE chamber
UPDATE pressurecontrol
4.2.2.49. ft_vw_qwat_network_element_delete¶
DELETE subscriber
DELETE part
DELETE vw_qwat_installation
DELETE hydrant
DELETE samplingpoint
DELETE valve
DELETE meter
DELETE vw_node_element
4.2.2.50. ft_vw_qwat_network_element_insert¶
INSERT vw_node_element
INSERT subscriber
INSERT part
INSERT vw_qwat_installation
INSERT hydrant
INSERT samplingpoint
INSERT valve
INSERT meter
4.2.2.51. ft_vw_qwat_network_element_update¶
UPDATE vw_node_element
UPDATE subscriber
UPDATE part
UPDATE vw_qwat_installation
UPDATE hydrant
UPDATE valve
UPDATE meter
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 = 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 ?
Warning
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
Warning
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¶
BEFORE INSERT - ft_geom3d_altitude
BEFORE UPDATE altitude, geometry - ft_geom3d_altitude
4.2.4.4. crossing¶
BEFORE UPDATE - ft_controled_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¶
BEFORE INSERT - ft_leak_pipe
BEFORE INSERT OR UPDATE repair_date - ft_leak_repaired
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¶
AFTER INSERT - ft_node_add_pipe_vertex
AFTER UPDATE geometry - ft_node_add_pipe_vertex
BEFORE UPDATE geometry_alt1, geometry_alt2 - ft_geometry_alternative_aux
BEFORE INSERT - ft_geometry_alternative_main
BEFORE UPDATE geometry - ft_geometry_alternative_main
BEFORE INSERT - ft_node_geom
BEFORE UPDATE geometry - ft_node_geom
AFTER UPDATE geometry - ft_pipe_node_moved
4.2.4.15. part¶
No triggers
4.2.4.16. pipe¶
BEFORE UPDATE OF geometry_alt1, geometry_alt2 - ft_geometry_alternative_aux
BEFORE INSERT - ft_geometry_alternative_main
BEFORE UPDATE geometry - ft_geometry_alternative_main
BEFORE INSERT - ft_pipe_geom
BEFORE UPDATE geometry - ft_pipe_geom
AFTER INSERT OR DELETE - ft_pipe_node_type
AFTER UPDATE geometry - ft_pipe_node_type
BEFORE INSERT OR UPDATE tunnel_or_bridge - ft_pipe_tunnelbridge
AFTER DELETE - ft_valve_pipe_update
AFTER UPDATE geometry - ft_valve_pipe_update
4.2.4.17. pressurecontrol¶
4.2.4.18. pressurezone¶
BEFORE UPDATE geometry_alt1, geometry_alt2 - ft_geometry_alternative_aux
BEFORE INSERT - ft_geometry_alternative_main
BEFORE UPDATE geometry - ft_geometry_alternative_main
4.2.4.19. printmap¶
4.2.4.20. protectionzone¶
4.2.4.21. pump¶
4.2.4.22. remote¶
BEFORE UPDATE geometry_alt1, geometry_alt2 - ft_geometry_alternative_aux
BEFORE INSERT - ft_geometry_alternative_main
BEFORE UPDATE geometry - ft_geometry_alternative_main
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¶
BEFORE INSERT - ft_geom3d_altitude
BEFORE UPDATE altitude, geometry - ft_geom3d_altitude
4.2.4.28. tank¶
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¶
AFTER UPDATE - ft_valve_update
BEFORE INSERT - ft_valve_handle_altitude
BEFORE UPDATE handle_altitude, handle_geometry - ft_valve_handle_altitude
AFTER INSERT - ft_valve_node_set_type
4.2.4.31. worker¶
No triggers
4.2.5. Views¶
4.2.5.1. vw_element_hydrant¶
DELETE - ft_element_hydrant_delete
INSERT - ft_element_hydrant_insert
UPDATE - ft_element_hydrant_update
4.2.5.2. vw_consumptionzone¶
No triggers
4.2.5.3. vw_element_installation¶
DELETE - ft_element_installation_delete
INSERT - ft_element_installation_insert
UPDATE - ft_element_installation_update
4.2.5.4. vw_element_meter¶
DELETE - ft_element_meter_delete
INSERT - ft_element_meter_insert
UPDATE - ft_element_meter_update
4.2.5.5. vw_element_part¶
DELETE - ft_element_part_delete
INSERT - ft_element_part_insert
UPDATE - ft_element_part_update
4.2.5.6. vw_element_samplingpoint¶
DELETE - ft_element_samplingpoint_delete
INSERT - ft_element_samplingpoint_insert
UPDATE - ft_element_samplingpoint_update
4.2.5.7. vw_element_subscriber¶
DELETE - ft_element_subscriber_delete
INSERT - ft_element_subscriber_insert
UPDATE - ft_element_subscriber_update
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¶
DELETE - ft_element_valve_delete
INSERT - ft_element_valve_insert
UPDATE - ft_element_valve_update
4.2.5.15. vw_installation_chamber¶
DELETE - ft_installation_chamber_delete
INSERT - ft_installation_chamber_insert
UPDATE - ft_installation_chamber_update
4.2.5.16. vw_installation_pressurecontrol¶
4.2.5.17. vw_installation_pump¶
DELETE - ft_installation_pump_delete
INSERT - ft_installation_pump_insert
UPDATE - ft_installation_pump_update
4.2.5.18. vw_installation_source¶
DELETE - ft_installation_source_delete
INSERT - ft_installation_source_insert
UPDATE - ft_installation_source_update
4.2.5.19. vw_installation_tank¶
DELETE - ft_installation_tank_delete
INSERT - ft_installation_tank_insert
UPDATE - ft_installation_tank_update
4.2.5.20. vw_installation_treatment¶
DELETE - ft_installation_treatment_delete
INSERT - ft_installation_treatment_insert
UPDATE - ft_installation_treatment_update
4.2.5.21. vw_leak¶
No triggers
4.2.5.22. vw_node_element¶
DELETE - ft_node_element_delete
INSERT - ft_node_element_insert
UPDATE - ft_node_element_update
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¶
4.2.5.29. vw_printmap¶
No triggers
4.2.5.30. vw_protectionzone¶
No triggers
4.2.5.31. vw_qwat_installation¶
DELETE - ft_vw_qwat_installation_delete
INSERT - ft_vw_qwat_installation_insert
UPDATE - ft_vw_qwat_installation_update
4.2.5.32. vw_qwat_network_element¶
DELETE - ft_vw_qwat_network_element_delete
INSERT - ft_vw_qwat_network_element_insert
UPDATE - ft_vw_qwat_network_element_update
4.2.5.33. vw_all_nodes¶
DELETE - ft_vw_all_nodes_delete
INSERT - ft_vw_all_nodes_insert
UPDATE - ft_vw_all_nodes_update
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