This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Using SQL Triggers to Populate Custom Properties

I wanted to share an advanced SQL technique that can simplify onboarding new nodes when you have several custom properties that are related. This comes in handy for a number of use cases. In this example, I'm updating a series of custom properties that are related to the city where a node resides. For me, this simplified adding new nodes to my google map integration, since I can't remember that exact latitude and longitude values for every site I manage in our Orion.

I should add that I urge you to use caution when adding triggers to your Orion database. There are some limitations that I won't go into detail here, but you should pick up a SQL book and do a little reading on the pro's and con's before you start designing your own triggers. A poorly designed trigger can wreak havoc on your server performance. If you have a DBA & they like beer or xbox cards, it might be a good tradeoff to pass your new trigger through them for their stamp of approval.

First, the following are some good notes to keep around:

/* !Show a list of MSSQL Triggers */

SELECT

    [so].[name] AS [trigger_name],

    USER_NAME([so].[uid]) AS [trigger_owner],

    USER_NAME([so2].[uid]) AS [table_schema],

    OBJECT_NAME([so].[parent_obj]) AS [table_name],

    OBJECTPROPERTY( [so].[id], 'ExecIsUpdateTrigger') AS [isupdate],

    OBJECTPROPERTY( [so].[id], 'ExecIsDeleteTrigger') AS [isdelete],

    OBJECTPROPERTY( [so].[id], 'ExecIsInsertTrigger') AS [isinsert],

    OBJECTPROPERTY( [so].[id], 'ExecIsAfterTrigger') AS [isafter],

    OBJECTPROPERTY( [so].[id], 'ExecIsInsteadOfTrigger') AS [isinsteadof],

    OBJECTPROPERTY([so].[id], 'ExecIsTriggerDisabled') AS [disabled]

FROM sysobjects AS [so]

INNER JOIN sysobjects AS so2 ON so.parent_obj = so2.Id

WHERE [so].[type] = 'TR'

/* !Enable or Disable Triggers */

ALTER TABLE Interfaces

<ENABLE | DISBLE> TRIGGER <ALL | <Trigger Name>>

/* !Delete Triggers */

DROP Trigger <Trigger Name>

Second, the trigger:

CREATE TRIGGER Update_Nodes_By_City ON NODES

    AFTER UPDATE

    AS

    IF UPDATE(City)

    BEGIN

            DECLARE @city VARCHAR(255)

                SELECT @city=City from Inserted           

                IF @city IS NOT NULL

                BEGIN

                    IF @city='Austin'

                    BEGIN

                        UPDATE n

                        Set n.latitude='30.285808', n.longitude='-97.826192', n.State='TX', n.Country='US'

                        FROM Nodes n

                        INNER JOIN Inserted i on i.NodeID=n.NodeID

                    END

                    ELSE IF @city='FtWorth'

                        BEGIN

                        UPDATE n

                        Set n.latitude='32.725409', n.longitude='-97.32085', n.State='TX', n.Country='US'

                        FROM Nodes n

                        INNER JOIN Inserted i on i.NodeID=n.NodeID

                    END

                    ELSE IF @city='Albany'

                        BEGIN

                        UPDATE n

                        Set n.latitude='42.652579', n.longitude='-73.756232', n.State='NY', n.Country='US'

                        FROM Nodes n

                        INNER JOIN Inserted i on i.NodeID=n.NodeID

                    END

                    ELSE IF @city='Tulsa'

                        BEGIN

                        UPDATE n

                        Set n.latitude='36.153982', n.longitude='-95.992775', n.State='OK', n.Country='US'

                        FROM Nodes n

                        INNER JOIN Inserted i on i.NodeID=n.NodeID

                    END

                    ELSE IF @city='Cork'

                        BEGIN

                        UPDATE n

                        Set n.latitude='51.897872', n.longitude='-8.471087', n.State='IRL', n.Country='Ireland'

                        FROM Nodes n

                        INNER JOIN Inserted i on i.NodeID=n.NodeID

                    END

                    ELSE IF @city='San_Antonio'

                        BEGIN

                        UPDATE n

                        Set n.latitude='29.424122', n.longitude='-98.493628', n.State='TX', n.Country='US'

                        FROM Nodes n

                        INNER JOIN Inserted i on i.NodeID=n.NodeID

                    END

                    ELSE IF @city='Portland'

                        BEGIN

                        UPDATE n

                        Set n.latitude='45.523452', n.longitude='-122.676207', n.State='OR', n.Country='US'

                        FROM Nodes n

                        INNER JOIN Inserted i on i.NodeID=n.NodeID

                    END

                END

    END





** One thing that I should note is that you need to disable triggers if you're working with some of the SDK features. I have to disable my triggers when adding inventory through the SDK, then simply turn them back on when done.

  • Very Nice!!  I hated setting my Lat and Long for the Google Integration I worked on last year.  This would have worked wonders for me back then.  Bookmarked for future use!


  • I took a different apporach. I built a table with all sites and their lat lon, SiteLatLon. I tag a custom property with a site code with a SQL script based on a set of rules that loop the device name through a set of rules. In the Google maps aspx I put this (indent formatting added for clarity):

    SELECT

    CASE

         WHEN Status=2 THEN 5

         WHEN Status=3 THEN 4

         WHEN Status=12 THEN 3

         WHEN Status=1 THEN 2

         WHEN Status=9 THEN 1

         WHEN Status=0 THEN 0

         ELSE Status

    END AS Rank, NodeID, StatusLED, City, Caption, LL.SiteLat as Latitude, LL.SiteLon as Longitude from Nodes N

    join SiteData SD on SD.Branch_ID = N._BRANCH_ID

    join SiteLatLon LL on LL.SiteCode = SD.LatLonCode

    where N.Zipcode IS NOT NULL and City IS NOT NULL

    order by Rank asc, city asc

    This works for us since our site IDs are numeric and that number is in each node's name.