<div dir="ltr">Using <a href="http://twiav-tt.blogspot.com/2013/04/postgis-trigger-function-keeping-track.html">this</a> tutorial, I've established a SQL trigger in order to track changes on a PostGIS layer. It works great for a point layer. However, using the script below, it causes problems with line layer, as all of the triggers occur twice.<br><br>I first realized this because the history table inserts lines for the 'INSERT' and 'UPDATE' logic twice (not for 'DELETE', surprisingly). The notices print to the console twice for 'INSERT' 'UPDATE' and 'DELETE'.<br><br>For example:<br><br>- When creating a line in the base table, the history table creates two objects with the same attributes except for the hid (history table id).<br><br>- When updating the attribute of an existing line, in the history table, three objects are created:<br>1) One with field 'etat' = 'MODIFICATION COURANTE' (resembles the existing feature in the base table, including the updated attribute)<br>2) Two with field 'etat' = 'MODIFICATION ARCHIVEE' -- one with the old attribute value and one with the new attribute value. At this point, I am expecting only one object containing the old attribute. The object containing the new attribute is out if place. <br><br><pre style="color:rgb(0,0,0);background-image:initial;background-position:initial;background-size:initial;background-repeat:initial;background-origin:initial;background-clip:initial"><span style="color:rgb(105,105,105)">--CREATE TRIGGER</span>
<span style="color:rgb(128,0,0);font-weight:bold">CREATE</span> <span style="color:rgb(128,0,0);font-weight:bold">or</span> <span style="color:rgb(128,0,0);font-weight:bold">REPLACE</span> <span style="color:rgb(128,0,0);font-weight:bold">FUNCTION</span> test<span style="color:rgb(128,128,48)">.</span>test_track_history_tracker<span style="color:rgb(128,128,48)">(</span><span style="color:rgb(128,128,48)">)</span> <span style="color:rgb(128,0,0);font-weight:bold">RETURNS</span> <span style="color:rgb(128,0,0);font-weight:bold">trigger</span> <span style="color:rgb(128,0,0);font-weight:bold">AS</span>
$new_test_track_history_tracker$
<span style="color:rgb(128,0,0);font-weight:bold">BEGIN</span>
<span style="color:rgb(105,105,105)">-- INSERT</span>
<span style="color:rgb(128,0,0);font-weight:bold">IF</span> <span style="color:rgb(128,128,48)">(</span>TG_OP <span style="color:rgb(128,128,48)">=</span> <span style="color:rgb(0,0,230)">'INSERT'</span><span style="color:rgb(128,128,48)">)</span> <span style="color:rgb(128,0,0);font-weight:bold">THEN</span>
<span style="color:rgb(128,0,0);font-weight:bold">INSERT</span> <span style="color:rgb(128,0,0);font-weight:bold">INTO</span> test<span style="color:rgb(128,128,48)">.</span><span style="color:rgb(128,0,0)">"Conduite_test_history"</span>
<span style="color:rgb(128,128,48)">(</span>diametre<span style="color:rgb(128,128,48)">,</span> <span style="color:rgb(128,0,0);font-weight:bold">type</span><span style="color:rgb(128,128,48)">,</span> materiau<span style="color:rgb(128,128,48)">,</span> origininfo<span style="color:rgb(128,128,48)">,</span> dtreno<span style="color:rgb(128,128,48)">,</span> dtpose<span style="color:rgb(128,128,48)">,</span> reparateur<span style="color:rgb(128,128,48)">,</span> dt_dmd<span style="color:rgb(128,128,48)">,</span> id_sig<span style="color:rgb(128,128,48)">,</span> gid_org<span style="color:rgb(128,128,48)">,</span> created<span style="color:rgb(128,128,48)">,</span> created_by<span style="color:rgb(128,128,48)">,</span> modified<span style="color:rgb(128,128,48)">,</span> etat<span style="color:rgb(128,128,48)">,</span> geom<span style="color:rgb(128,128,48)">)</span>
<span style="color:rgb(128,0,0);font-weight:bold">VALUES</span>
<span style="color:rgb(128,128,48)">(</span><span style="color:rgb(128,0,0);font-weight:bold">NEW</span><span style="color:rgb(128,128,48)">.</span>diametre<span style="color:rgb(128,128,48)">,</span> <span style="color:rgb(128,0,0);font-weight:bold">NEW</span><span style="color:rgb(128,128,48)">.</span><span style="color:rgb(128,0,0);font-weight:bold">type</span><span style="color:rgb(128,128,48)">,</span> <span style="color:rgb(128,0,0);font-weight:bold">NEW</span><span style="color:rgb(128,128,48)">.</span>materiau<span style="color:rgb(128,128,48)">,</span> <span style="color:rgb(128,0,0);font-weight:bold">NEW</span><span style="color:rgb(128,128,48)">.</span>origininfo<span style="color:rgb(128,128,48)">,</span> <span style="color:rgb(128,0,0);font-weight:bold">NEW</span><span style="color:rgb(128,128,48)">.</span>dtreno<span style="color:rgb(128,128,48)">,</span> <span style="color:rgb(128,0,0);font-weight:bold">NEW</span><span style="color:rgb(128,128,48)">.</span>dtpose<span style="color:rgb(128,128,48)">,</span> <span style="color:rgb(128,0,0);font-weight:bold">NEW</span><span style="color:rgb(128,128,48)">.</span>reparateur<span style="color:rgb(128,128,48)">,</span> <span style="color:rgb(128,0,0);font-weight:bold">NEW</span><span style="color:rgb(128,128,48)">.</span>dt_dmd<span style="color:rgb(128,128,48)">,</span> <span style="color:rgb(128,0,0);font-weight:bold">NEW</span><span style="color:rgb(128,128,48)">.</span>id_sig<span style="color:rgb(128,128,48)">,</span> <span style="color:rgb(128,0,0);font-weight:bold">NEW</span><span style="color:rgb(128,128,48)">.</span>gid<span style="color:rgb(128,128,48)">,</span> <span style="color:rgb(128,0,0);font-weight:bold">current_timestamp</span><span style="color:rgb(128,128,48)">,</span>
<span style="color:rgb(128,0,0);font-weight:bold">current_user</span><span style="color:rgb(128,128,48)">,</span> <span style="color:rgb(128,0,0);font-weight:bold">FALSE</span><span style="color:rgb(128,128,48)">,</span> <span style="color:rgb(0,0,230)">'CREATION'</span><span style="color:rgb(128,128,48)">,</span> <span style="color:rgb(128,0,0);font-weight:bold">NEW</span><span style="color:rgb(128,128,48)">.</span>geom<span style="color:rgb(128,128,48)">)</span><span style="color:rgb(128,128,48)">;</span>
<span style="color:rgb(128,0,0);font-weight:bold">raise</span> notice <span style="color:rgb(0,0,230)">'Insert happened'</span><span style="color:rgb(128,128,48)">;</span>
<span style="color:rgb(128,0,0);font-weight:bold">RETURN</span> <span style="color:rgb(128,0,0);font-weight:bold">NEW</span><span style="color:rgb(128,128,48)">;</span>
<span style="color:rgb(105,105,105)">-- UPDATE</span>
ELSEIF <span style="color:rgb(128,128,48)">(</span>TG_OP <span style="color:rgb(128,128,48)">=</span> <span style="color:rgb(0,0,230)">'UPDATE'</span><span style="color:rgb(128,128,48)">)</span> <span style="color:rgb(128,0,0);font-weight:bold">THEN</span>
<span style="color:rgb(128,0,0);font-weight:bold">UPDATE</span> test<span style="color:rgb(128,128,48)">.</span><span style="color:rgb(128,0,0)">"Conduite_test_history"</span>
<span style="color:rgb(128,0,0);font-weight:bold">SET</span> deleted <span style="color:rgb(128,128,48)">=</span> <span style="color:rgb(128,0,0);font-weight:bold">current_timestamp</span><span style="color:rgb(128,128,48)">,</span> deleted_by <span style="color:rgb(128,128,48)">=</span> <span style="color:rgb(128,0,0);font-weight:bold">current_user</span><span style="color:rgb(128,128,48)">,</span> modified <span style="color:rgb(128,128,48)">=</span> <span style="color:rgb(128,0,0);font-weight:bold">TRUE</span><span style="color:rgb(128,128,48)">,</span> etat <span style="color:rgb(128,128,48)">=</span> <span style="color:rgb(0,0,230)">'MODIFICATION ARCHIVEE'</span>
<span style="color:rgb(128,0,0);font-weight:bold">WHERE</span> deleted <span style="color:rgb(128,0,0);font-weight:bold">IS</span> <span style="color:rgb(128,0,0);font-weight:bold">NULL</span> <span style="color:rgb(128,0,0);font-weight:bold">and</span> gid_org <span style="color:rgb(128,128,48)">=</span> <span style="color:rgb(128,0,0);font-weight:bold">OLD</span><span style="color:rgb(128,128,48)">.</span>gid<span style="color:rgb(128,128,48)">;</span>
<span style="color:rgb(128,0,0);font-weight:bold">INSERT</span> <span style="color:rgb(128,0,0);font-weight:bold">INTO</span> test<span style="color:rgb(128,128,48)">.</span><span style="color:rgb(128,0,0)">"Conduite_test_history"</span>
<span style="color:rgb(128,128,48)">(</span>diametre<span style="color:rgb(128,128,48)">,</span> <span style="color:rgb(128,0,0);font-weight:bold">type</span><span style="color:rgb(128,128,48)">,</span> materiau<span style="color:rgb(128,128,48)">,</span> origininfo<span style="color:rgb(128,128,48)">,</span> dtreno<span style="color:rgb(128,128,48)">,</span> dtpose<span style="color:rgb(128,128,48)">,</span> reparateur<span style="color:rgb(128,128,48)">,</span> dt_dmd<span style="color:rgb(128,128,48)">,</span> id_sig<span style="color:rgb(128,128,48)">,</span> gid_org<span style="color:rgb(128,128,48)">,</span> created<span style="color:rgb(128,128,48)">,</span> created_by<span style="color:rgb(128,128,48)">,</span> modified<span style="color:rgb(128,128,48)">,</span> etat<span style="color:rgb(128,128,48)">,</span> geom<span style="color:rgb(128,128,48)">)</span>
<span style="color:rgb(128,0,0);font-weight:bold">VALUES</span>
<span style="color:rgb(128,128,48)">(</span><span style="color:rgb(128,0,0);font-weight:bold">NEW</span><span style="color:rgb(128,128,48)">.</span>diametre<span style="color:rgb(128,128,48)">,</span> <span style="color:rgb(128,0,0);font-weight:bold">NEW</span><span style="color:rgb(128,128,48)">.</span><span style="color:rgb(128,0,0);font-weight:bold">type</span><span style="color:rgb(128,128,48)">,</span> <span style="color:rgb(128,0,0);font-weight:bold">NEW</span><span style="color:rgb(128,128,48)">.</span>materiau<span style="color:rgb(128,128,48)">,</span> <span style="color:rgb(128,0,0);font-weight:bold">NEW</span><span style="color:rgb(128,128,48)">.</span>origininfo<span style="color:rgb(128,128,48)">,</span> <span style="color:rgb(128,0,0);font-weight:bold">NEW</span><span style="color:rgb(128,128,48)">.</span>dtreno<span style="color:rgb(128,128,48)">,</span> <span style="color:rgb(128,0,0);font-weight:bold">NEW</span><span style="color:rgb(128,128,48)">.</span>dtpose<span style="color:rgb(128,128,48)">,</span> <span style="color:rgb(128,0,0);font-weight:bold">NEW</span><span style="color:rgb(128,128,48)">.</span>reparateur<span style="color:rgb(128,128,48)">,</span> <span style="color:rgb(128,0,0);font-weight:bold">NEW</span><span style="color:rgb(128,128,48)">.</span>dt_dmd<span style="color:rgb(128,128,48)">,</span> <span style="color:rgb(128,0,0);font-weight:bold">NEW</span><span style="color:rgb(128,128,48)">.</span>id_sig<span style="color:rgb(128,128,48)">,</span> <span style="color:rgb(128,0,0);font-weight:bold">NEW</span><span style="color:rgb(128,128,48)">.</span>gid<span style="color:rgb(128,128,48)">,</span> <span style="color:rgb(128,0,0);font-weight:bold">current_timestamp</span><span style="color:rgb(128,128,48)">,</span>
<span style="color:rgb(128,0,0);font-weight:bold">current_user</span><span style="color:rgb(128,128,48)">,</span> <span style="color:rgb(128,0,0);font-weight:bold">FALSE</span><span style="color:rgb(128,128,48)">,</span> <span style="color:rgb(0,0,230)">'MODIFICATION COURANTE'</span><span style="color:rgb(128,128,48)">,</span> <span style="color:rgb(128,0,0);font-weight:bold">NEW</span><span style="color:rgb(128,128,48)">.</span>geom<span style="color:rgb(128,128,48)">)</span><span style="color:rgb(128,128,48)">;</span>
<span style="color:rgb(128,0,0);font-weight:bold">raise</span> notice <span style="color:rgb(0,0,230)">'Update happened'</span><span style="color:rgb(128,128,48)">;</span>
<span style="color:rgb(128,0,0);font-weight:bold">RETURN</span> <span style="color:rgb(128,0,0);font-weight:bold">NEW</span><span style="color:rgb(128,128,48)">;</span>
<span style="color:rgb(105,105,105)">-- DELETE</span>
ELSEIF <span style="color:rgb(128,128,48)">(</span>TG_OP <span style="color:rgb(128,128,48)">=</span> <span style="color:rgb(0,0,230)">'DELETE'</span><span style="color:rgb(128,128,48)">)</span> <span style="color:rgb(128,0,0);font-weight:bold">THEN</span>
<span style="color:rgb(128,0,0);font-weight:bold">UPDATE</span> test<span style="color:rgb(128,128,48)">.</span><span style="color:rgb(128,0,0)">"Conduite_test_history"</span>
<span style="color:rgb(128,0,0);font-weight:bold">SET</span> deleted <span style="color:rgb(128,128,48)">=</span> <span style="color:rgb(128,0,0);font-weight:bold">current_timestamp</span><span style="color:rgb(128,128,48)">,</span> deleted_by <span style="color:rgb(128,128,48)">=</span> <span style="color:rgb(128,0,0);font-weight:bold">current_user</span><span style="color:rgb(128,128,48)">,</span> etat <span style="color:rgb(128,128,48)">=</span> <span style="color:rgb(0,0,230)">'SUPPRESSION'</span>
<span style="color:rgb(128,0,0);font-weight:bold">WHERE</span> deleted <span style="color:rgb(128,0,0);font-weight:bold">is</span> <span style="color:rgb(128,0,0);font-weight:bold">NULL</span> <span style="color:rgb(128,0,0);font-weight:bold">and</span> gid_org <span style="color:rgb(128,128,48)">=</span> <span style="color:rgb(128,0,0);font-weight:bold">OLD</span><span style="color:rgb(128,128,48)">.</span>gid<span style="color:rgb(128,128,48)">;</span>
<span style="color:rgb(128,0,0);font-weight:bold">raise</span> notice <span style="color:rgb(0,0,230)">'Delete happened'</span><span style="color:rgb(128,128,48)">;</span>
<span style="color:rgb(128,0,0);font-weight:bold">RETURN</span> <span style="color:rgb(128,0,0);font-weight:bold">NULL</span><span style="color:rgb(128,128,48)">;</span>
<span style="color:rgb(128,0,0);font-weight:bold">END IF</span><span style="color:rgb(128,128,48)">;</span>
<span style="color:rgb(128,0,0);font-weight:bold">END</span><span style="color:rgb(128,128,48)">;</span>
$new_test_track_history_tracker$ <span style="color:rgb(128,0,0);font-weight:bold">LANGUAGE</span> plpgsql<span style="color:rgb(128,128,48)">;</span>
<span style="color:rgb(105,105,105)">--ADD TRIGGER</span>
<span style="color:rgb(128,0,0);font-weight:bold">DROP</span> <span style="color:rgb(128,0,0);font-weight:bold">TRIGGER</span> <span style="color:rgb(128,0,0);font-weight:bold">IF</span> <span style="color:rgb(128,0,0);font-weight:bold">EXISTS</span> trg_test_track_history_tracker <span style="color:rgb(128,0,0);font-weight:bold">ON</span> test<span style="color:rgb(128,128,48)">.</span><span style="color:rgb(128,0,0)">"Conduite_test"</span><span style="color:rgb(128,128,48)">;</span>
<span style="color:rgb(128,0,0);font-weight:bold">CREATE</span> <span style="color:rgb(128,0,0);font-weight:bold">TRIGGER</span> trg_test_track_history_tracker <span style="color:rgb(128,0,0);font-weight:bold">AFTER</span> <span style="color:rgb(128,0,0);font-weight:bold">INSERT</span> <span style="color:rgb(128,0,0);font-weight:bold">OR</span> <span style="color:rgb(128,0,0);font-weight:bold">UPDATE</span> <span style="color:rgb(128,0,0);font-weight:bold">OR</span> <span style="color:rgb(128,0,0);font-weight:bold">DELETE</span> <span style="color:rgb(128,0,0);font-weight:bold">ON</span>
test<span style="color:rgb(128,128,48)">.</span><span style="color:rgb(128,0,0)">"Conduite_test"</span>
<span style="color:rgb(128,0,0);font-weight:bold">FOR</span> <span style="color:rgb(128,0,0);font-weight:bold">EACH</span> <span style="color:rgb(128,0,0);font-weight:bold">ROW</span> <span style="color:rgb(128,0,0);font-weight:bold">EXECUTE</span> <span style="color:rgb(128,0,0);font-weight:bold">PROCEDURE</span> test<span style="color:rgb(128,128,48)">.</span>test_track_history_tracker<span style="color:rgb(128,128,48)">(</span><span style="color:rgb(128,128,48)">)</span><span style="color:rgb(128,128,48)">;</span>
</pre></div>