Monitor your instance for accidental changes

You know when you have a test instance, and out of the blue users report something is not working as before. One of the probable reasons for this (of course, there are many more) is someone made a change on another model / layer that is not getting noticed, and may overshadow good code or create a different behavior.

If you are doing development in CUS layer, normally you are coding in a different model than the CUS Model default, so what you would want to a way to monitor if you get changes in CUS model or USR model.

(do NOT use this in LIVE systems)
I am using a trigger in the model database, that sends me an email everytime I get a new element in unwanted locations:

CREATE TRIGGER [dbo].[MonitorUnwantedLocation]
   ON  [dbo].[ModelElementData]
   AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    --select * from Layer
    --select * from ModelManifest

    if ((select count(*) from ModelElement me
        join ModelElementData med on med.ElementHandle = me.ElementHandle
        join ElementTypes et on et.ElementType = me.ElementType
        join ModelElement meRoot on meRoot.ElementHandle = me.RootHandle
        join ModelManifest mm on mm.ModelId = med.ModelId
        join Layer l on l.Id = med.LayerId
        join inserted i on i.ElementHandle = med.ElementHandle
            where (med.ModelId = 13 or med.ModelId = 15)) --(mm.[Name] = 'CUS Model' or mm.[Name] = 'USR Model'))
        > 0)
    begin
        EXEC msdb.dbo.sp_send_dbmail 
            @profile_name = 'YourPublicProfile', 
            @recipients = 'iulian.cordobin@axfaq.com', 
            @subject = 'Unwanted objects [USR/CUS]' ; 
    end
END
GO

ALTER TABLE [dbo].[ModelElementData] ENABLE TRIGGER [MonitorUnwantedLocation]
GO


No comments:

Post a Comment