How to enable editor tracking on SQL Server Spatial Tables

[Sandanuwan Dhanushka]

Dear All,

I'm new to QGIS (coming from an ArcGIS background). Currently, I'm working with a council that requires user tracking for GIS tables to be enabled. We need to identify the following details for each record on the spatial tables. We are using the Microsoft SQL Server Spatial 2019 database

1. Created user
2. Created date
3. Last edited user
4. Last edited date

I'm wondering whether this is possible with QGIS and how to achieve that. 

Similar capability on ArcGIS Pro: Enable editor tracking—ArcGIS Pro | Documentation

Thanks & Regards,

Dhan

[John Bryant]

Hi Dhan, I've implemented something similar in PostGIS, essentially:

• create 2 trigger functions, one for new records (populate create & edit columns) and one for updates (leave create columns alone, update edit columns)
• add the appropriate columns and triggers to each table you want to track edits on

All done on the database side, no special config required on the QGIS side.

My trigger functions usually look something like this:

create or replace function et_create_row()

returns trigger as $$

begin

new.created_by = user;

new.created_at = now();

new.edited_by = user;

new.edited_at = now();

return new;

end;

$$ language plpgsql;

create or replace function et_update_row()

returns trigger as $$

begin

new.edited_by = user;

new.edited_at = now();

return new;

end;

$$ language plpgsql;

Sorry I don't know the SQL Server equivalent (ChatGPT should be able to help though!)...

Cheers

John

--
You received this message because you are subscribed to the Google Groups "QGIS Australia User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to australian-qgis-use...@googlegroups.com.
To view this discussion, visit https://groups.google.com/d/msgid/australian-qgis-user-group/8602460e-4ba3-4c9f-9342-066bdf75bb86n%40googlegroups.com.

[Andrew Jeffrey]

Nice one John, that looks like a solid solution.

You can go full QGIS project side solution using default values in the attribute form, provided you have the fields in the dataset ready to go.

For example getting the date created, you could use the "now()" date function to auto-populate the date into a "create_on" field.

For user you can use the "user_full_name" variable.

Then to capture the last updated, you'd do the same process but check the "Apply default value on update" to recapture the info when the record is edited.

There's a good article here about the feature - https://gis.stackexchange.com/questions/211120/setting-default-value-for-attributes-in-shapefiles-using-qgis

To view this discussion, visit https://groups.google.com/d/msgid/australian-qgis-user-group/CAHY5hn9rQ9fiJH1T9WDqwCUwhRsuc1f7kTLzV2QzK7f0hntTCQ%40mail.gmail.com.

[sanda...@gmail.com]

Thank you both, and appreciate the support! I'll test these options.

It's great to see we have an active QGIS community in AUS. Keep up the good work! 

Regards,

Dhan