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.