{"id":87782,"date":"2020-08-10T01:43:07","date_gmt":"2020-08-10T01:43:07","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=87782"},"modified":"2020-08-18T13:16:03","modified_gmt":"2020-08-18T13:16:03","slug":"how-to-modify-a-sql-server-graph-edge-with-t-sql","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/how-to-modify-a-sql-server-graph-edge-with-t-sql\/","title":{"rendered":"How to modify a SQL Server Graph Edge with T-SQL"},"content":{"rendered":"

As I have been writing a section on SQL Server graph tables in my Database Design book<\/a>, (and prepping for a hopeful book on the subject next year), I find that there are a few really annoying things about dealing with graph tables. This blog serves to clear up the first, most annoying of them. Inserting, updating, and deleting edges.<\/p>\n

Because the key values in the graph database structures are hidden, you can’t just insert a new edge without translating your table’s key values to the graph database internal values. Edges aren’t even available for an update of the from or to references. As I wrote stored procedures to do this, I realized “why not use a view and trigger to make this happen”. So I did. The result is that I can insert, delete, and even update graph tables using normal SQL syntax. What makes this better than the stored procedure is that I can insert multiple rows simultaneously.<\/p>\n

I haven’t tried to do this with large number of rows yet, but either way it will work fine for one or two rows in any sized table with proper indexing. To demonstrate this,\u00a0I started with the following tables. There is no theme to the tables, there are just tables I have used to test out concepts like this:<\/p>\n

USE tempdb;\r\nGO\r\n\r\nCREATE SCHEMA Basics;\r\nGO\r\n\r\nCREATE TABLE Basics.Node1\r\n(\r\n    Node1Id int NOT NULL CONSTRAINT PKNode1 PRIMARY KEY\r\n)AS NODE;\r\n\r\nCREATE TABLE Basics.Node2\r\n(\r\n    Node2Id int NOT NULL CONSTRAINT PKNode2 PRIMARY KEY\r\n)AS NODE;\r\n\r\nCREATE TABLE Basics.BetweenNodes1\r\n(\r\n    ConnectedSinceTime datetime2(0) NOT NULL \r\n         CONSTRAINT DFLTBetweenNodes1_ConnectedSinceTime \r\n                                    DEFAULT(SYSDATETIME()),\r\n    CONSTRAINT ECBetweenNodes1_Node1_Node2 \r\n         CONNECTION(Basics.Node1 TO Basics.Node2)ON DELETE NO ACTION\r\n)AS EDGE;<\/pre>\n
Then insert a few rows into the two node tables to get things prepared for the edge triggers.<\/p>\n
INSERT INTO Basics.Node1(Node1Id)\r\nVALUES (1001),(1002),(1011),(1012),(1021);\r\n\r\nINSERT INTO Basics.Node2(Node2Id)\r\nVALUES (2011),(2012),(2021);<\/pre>\n
Next I create the following view that uses regular joins between the nodes to fetch the internal details of the graph structures. All that will be output by the view is just the primary key values of the two tables.<\/p>\n
CREATE OR ALTER VIEW Basics.BetweenNodes1_Manage_Node1_To_Node2\r\nAS\r\nSELECT Node1.Node1Id AS From_Node1Id, \r\n       Node2.Node2Id AS To_Node2Id\r\nFROM Basics.Node1,\r\n\u00a0 \u00a0\u00a0 Basics.BetweenNodes1,\r\n     Basics.Node2\r\nWHERE MATCH(Node1-(BetweenNodes1)->Node2);<\/pre>\n
There is no data in the edge yet, but before I insert any, I will create the following INSTEAD OF INSERT trigger.<\/p>\n
CREATE OR ALTER TRIGGER Basics.BetweenNodes1_Manage_Node1_To_Node2$InsertTrigger\r\nON Basics.BetweenNodes1_Manage_Node1_To_Node2\r\nINSTEAD OF INSERT\r\nAS\r\nSET NOCOUNT ON;\r\n--note, to keep it simple, only including the insert statement. Could \r\n--use more error handling for a production version of the trigger\r\n BEGIN \r\n  INSERT INTO Basics.BetweenNodes1($From_id, $To_id)\r\n  SELECT Node1.$node_id, Node2.$node_id\r\n  FROM Inserted\r\n       JOIN Basics.Node1\r\n           ON Node1.Node1Id = inserted.From_Node1Id\r\n       JOIN Basics.Node2 \r\n           ON Node2.Node2Id = inserted.To_Node2Id\r\n END;<\/pre>\n
Now insert one row into the table, just using the keys of the nodes:<\/p>\n
INSERT INTO Basics.BetweenNodes1_Manage_Node1_To_Node2(From_Node1Id, To_Node2Id)\r\nVALUES (1001, 2011);<\/pre>\n
Then view the output:<\/p>\n
SELECT * \r\nFROM Basics.BetweenNodes1_Manage_Node1_To_Node2;<\/pre>\n
You can see the rows have been inserted:<\/p>\n
Node1Id\u00a0 \u00a0 \u00a0Node2Id<\/code>
\n----------- -----------<\/code>
\n1001\u00a0 \u00a0 \u00a0 \u00a0 2011<\/code><\/p>\n
Then add multiple edge rows:<\/p>\n
INSERT INTO Basics.BetweenNodes1_Manage_Node1_To_Node2(From_Node1Id, To_Node2Id)\r\nVALUES (1002, 2012),(1002,2021);<\/pre>\n
Those rows were also added:<\/p>\n
Node1Id\u00a0 \u00a0 \u00a0Node2Id<\/code>
\n----------- -----------<\/code>
\n1001\u00a0 \u00a0 \u00a0 \u00a0 2011<\/code>
\n1002\u00a0 \u00a0 \u00a0 \u00a0 2012<\/code>
\n1002\u00a0 \u00a0 \u00a0 \u00a0 2021<\/code><\/p>\n
Next I add an update trigger. You can’t update an edge, so this turns into a delete, and then an update.<\/p>\n
CREATE OR ALTER TRIGGER Basics.BetweenNodes1_Manage_Node1_To_Node2$UpdateTrigger\r\nON Basics.BetweenNodes1_Manage_Node1_To_Node2\r\nINSTEAD OF UPDATE\r\nAS\r\n  BEGIN\r\n     SET NOCOUNT ON;\r\n     --delete rows in deleted\r\n     DELETE FROM Basics.BetweenNodes1\r\n     FROM Deleted\r\n         JOIN Basics.Node1\r\n             ON Node1.Node1Id = deleted.From_Node1Id\r\n         JOIN Basics.Node2 \r\n             ON Node2.Node2Id = deleted.To_Node2Id\r\n     --get the rows to delete by joining to the table\r\n     --through the graph keys\r\n          JOIN Basics.BetweenNodes1\r\n             ON Node1.$node_id = BetweenNodes1.$from_id\r\n                AND Node2.$node_id = BetweenNodes1.$to_id;\r\n\r\n      --add the rows from inserted\r\n      INSERT INTO Basics.BetweenNodes1($From_id, $To_id)\r\n      SELECT Node1.$node_id, Node2.$node_id\r\n      FROM Inserted\r\n           JOIN Basics.Node1\r\n              ON Node1.Node1Id = inserted.From_Node1Id\r\n           JOIN Basics.Node2 \r\n              ON Node2.Node2Id = inserted.To_Node2Id;\r\n END;<\/pre>\n
This is how the data looks now:<\/p>\n
Node1Id\u00a0 \u00a0 \u00a0Node2Id<\/code>
\n----------- -----------<\/code>
\n1001\u00a0 \u00a0 \u00a0 \u00a0 2011<\/code>
\n1002\u00a0 \u00a0 \u00a0 \u00a0 2012<\/code>
\n1002\u00a0 \u00a0 \u00a0 \u00a0 2021<\/code><\/p>\n
Update the rows using the view:<\/p>\n
UPDATE Basics.BetweenNodes1_Manage_Node1_To_Node2\r\nSET    From_Node1Id = 1001,\r\n       To_Node2Id = 2012\r\nWHERE  From_Node1Id = 1001\r\n  AND  To_Node2Id = 2011;<\/pre>\n
You can see that the 1001 row now is connected to 2012:<\/p>\n
Node1Id\u00a0 \u00a0 \u00a0Node2Id<\/code>
\n----------- -----------<\/code>
\n1001\u00a0 \u00a0 \u00a0 \u00a0 2012<\/code>
\n1002\u00a0 \u00a0 \u00a0 \u00a0 2012<\/code>
\n1002\u00a0 \u00a0 \u00a0 \u00a0 2021<\/code><\/p>\n
Now, update all of the rows to be connected to Node2Id = 2011.<\/p>\n
UPDATE Basics.BetweenNodes1_Manage_Node1_To_Node2\r\nSET To_Node2Id = 2011;<\/pre>\n
Check out the data and you will see the following:<\/p>\n
Node1Id\u00a0 \u00a0 \u00a0Node2Id<\/code>
\n----------- -----------<\/code>
\n1001\u00a0 \u00a0 \u00a0 \u00a0 2011<\/code>
\n1002\u00a0 \u00a0 \u00a0 \u00a0 2011<\/code>
\n1002\u00a0 \u00a0 \u00a0 \u00a0 2011<\/code><\/p>\n
Finally, I will implement an INSTEAD OF DELETE trigger:<\/p>\n
CREATE OR ALTER TRIGGER Basics.BetweenNodes1_Manage_Node1_To_Node2$UpdateTrigger\r\nON Basics.BetweenNodes1_Manage_Node1_To_Node2\r\nINSTEAD OF DELETE\r\nAS\r\n  BEGIN\r\n    SET NOCOUNT ON;\r\n    --delete rows in deleted\r\n    DELETE FROM Basics.BetweenNodes1\r\n    FROM Deleted\r\n          JOIN Basics.Node1\r\n             ON Node1.Node1Id = deleted.From_Node1Id\r\n          JOIN Basics.Node2 \r\n             ON Node2.Node2Id = deleted.To_Node2Id\r\n    \r\n     --get the rows to delete by joining to the table\r\n     --through the graph keys\r\n          JOIN Basics.BetweenNodes1\r\n             ON Node1.$node_id = BetweenNodes1.$from_id\r\n               AND Node2.$node_id = BetweenNodes1.$to_id;\r\n  END;<\/pre>\n
Delete the row where node1Id = 1001:<\/p>\n
DELETE FROM Basics.BetweenNodes1_Manage_Node1_To_Node2\r\nWHERE From_Node1Id = 1001;<\/pre>\n
Check out the data and you will see the following:<\/p>\n
Node1Id\u00a0 \u00a0 \u00a0Node2Id<\/code>
\n----------- -----------<\/code>
\n1002\u00a0 \u00a0 \u00a0 \u00a0 2011<\/code>
\n1002\u00a0 \u00a0 \u00a0 \u00a0 2011<\/code><\/p>\n
Finally, delete the last two rows:<\/p>\n
DELETE FROM Basics.BetweenNodes1_Manage_Node1_To_Node2;<\/pre>\n
The table is empty now. Next trick is to build a code generator to build a view for every edge without having to write this code over and over.<\/p>\n","protected":false},"excerpt":{"rendered":"
As I have been writing a section on SQL Server graph tables in my Database Design book, (and prepping for a hopeful book on the subject next year), I find that there are a few really annoying things about dealing with graph tables. This blog serves to clear up the first, most annoying of them….…<\/p>\n","protected":false},"author":56085,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[123644],"coauthors":[19684],"class_list":["post-87782","post","type-post","status-publish","format-standard","hentry","category-blogs","tag-t-sqlsql-graph-triggers"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/87782","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/56085"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=87782"}],"version-history":[{"count":7,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/87782\/revisions"}],"predecessor-version":[{"id":87851,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/87782\/revisions\/87851"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=87782"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=87782"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=87782"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=87782"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}