Version 2

    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.