2

I have made a featureclass for a utility company where they can register measures set up to mitigate problems with rats, called "mitigation measure". From this feature class it is possible to state if the current mitigation measure has been removed or not. This field is named "Removed" and it is a domain value with possibilities "Yes", "No" and "Not set".

I now wish to create a sql trigger that can update a time field called "DateRemove" with the current timestamp for the moment the utility company changes the field "Removed" from "No" to "Yes". But only when this exact change happens, not when other changes occur within the feature class.

Improve this question
  • Which RDBMS are you using? – Philᵀᴹ Jan 7 '16 at 10:56
  • Hey Phil. I am using Microsoft SQL Server 2008. Is that information enough? – Kristian Birchvald Jensen Jan 7 '16 at 11:22
  • 2
    I think this is what you're looking for. – Nelz Jan 7 '16 at 11:27
  • see @NelsonCasanova link, just add 'AND removed = 'Yes'' to where clause in (select 1 from inserted where ) – simplexio Jan 7 '16 at 11:43
  • So something like this: ALTER TRIGGER [sonfor_ags].[trg_date] ON [sonfor_ags].[MitigationMeasure] AFTER UPDATE AS BEGIN SET NOCOUNT ON; UPDATE t SET DateRemove = CURRENT_TIMESTAMP FROM [sonfor_ags].[MitigationMeasure] AS t INNER JOIN inserted AS i ON t.ID = i.ID AND t.Removed = 1; END GO , where 1="yes" – Kristian Birchvald Jensen Jan 7 '16 at 11:52
1

I changed a few things so that it ended up like this: 

Create TRIGGER [sonfor_ags].[trg_date] ON [sonfor_ags].[MitigationMeasure] 
AFTER UPDATE 
AS BEGIN 
SET NOCOUNT ON; 

    UPDATE t SET DateRemove = CURRENT_TIMESTAMP 
    FROM [sonfor_ags].[MitigationMeasure] AS t 
    INNER JOIN inserted AS i ON t.OBJECTID = i.OBJECTID 
       AND i.Removed = 1; END GO

and now it works perfectly. Thank you very much

Improve this answer
  • 1
    Yes and no. If you make any changes to MitigationMeasure after removed is set to 1 it will continue to update your column. Add a check to see if the DateRemoved is NULL if you don't want it to continue to move forward. – Kenneth Fisher Jan 7 '16 at 17:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.