The dummy root is probably the right track, except you don't actually have to create a dummy root to make multiple roots. Your table also appears to be simultaneously an adjacency pair as well, so you've got a couple of immediate options on how you want to go using the table. Both should probably be tested for suitability, as I have a sneaky feeling that depending on read-a-lot or write-a-lot usage, your mileage from either case may vary.
Since the needs of both your developers and fellow DBAs must be considered, most of the other options are probably out of the question ( though there are loads and loads ). This SQL Fiddle illustrates a couple of ways to leverage the materialized paths inherit in your structure, first through leveraging a multiple-root HIERARCHYID
, then through adjacency pair traversal through recursive CTEs.
To implement multiple roots, the only real "trick" to it is to preserve the sub-sequence on the root level. Instead of simply calling NULL
descendants during root node creation, like so,
INSERT INTO dbo.FailCode ( IDOrg, Name )
VALUES ( HIERARCHYID::GetRoot().GetDescendant( NULL, NULL ), '1' );
each root node can be created using the root as the base level ( though the first one obviously doesn't matter ):
INSERT INTO dbo.FailCode ( IDOrg, Name )
VALUES ( HIERARCHYID::GetRoot().GetDescendant( (
SELECT MAX( IDOrg )
FROM dbo.FailCode
WHERE IDOrg.GetAncestor( 1 ) = HIERARCHYID::GetRoot() ), NULL ), '1' );
INSERT INTO dbo.FailCode ( IDOrg, Name )
VALUES ( HIERARCHYID::GetRoot().GetDescendant( (
SELECT MAX( IDOrg )
FROM dbo.FailCode
WHERE IDOrg.GetAncestor( 1 ) = HIERARCHYID::GetRoot() ), NULL ), '2' );
If you're in more of an UPDATE
situtation, you can create the multiple root nodes off the primary key, if you like:
SELECT CAST( '/' + CONVERT( VARCHAR( 16 ), ID ) + '/' AS HIERARCHYID ).ToString()
FROM dbo.FailCode
WHERE Name IN ( '1', '2' );
Children can then be added to each root node as necessary, business as usual stuff from there.
On the adjacency pairs side of your structure, either of the above inserts would do the trick - the ParentID
for any root node just has to be NULL
. Again, at this point, adding children is pretty evident.
Either way you go about it, the hierarchy capabilities are largely equivalent and either solution can be effective and efficient ( though the adjacency list model will certainly appear to be a little more involved! ).
HIERARCHYID
SELECT Org = IDOrg.ToString(), ID, Name
FROM dbo.FailCode
ORDER BY ID;
Adjacency List
;WITH cte_AdjacencyList AS (
SELECT Lvl = ROW_NUMBER() OVER (
ORDER BY ID ),
Org = CAST( '/' + CONVERT( VARCHAR( 16 ), ROW_NUMBER() OVER (
ORDER BY ID ) ) + '/' AS VARCHAR( 32 ) ),
ParentID, ID, Name
FROM dbo.FailCode
WHERE ParentID IS NULL
UNION ALL
SELECT Lvl = ROW_NUMBER() OVER (
ORDER BY fc.ID ),
Org = CAST ( al.Org + CONVERT( VARCHAR( 16 ), ROW_NUMBER() OVER (
ORDER BY fc.ID ) ) + '/' AS VARCHAR( 32 ) ),
fc.ParentID, fc.ID, fc.Name
FROM dbo.FailCode fc
INNER JOIN cte_AdjacencyList al
ON fc.ParentID = al.ID )
SELECT Org, ID, Name
FROM cte_AdjacencyList
ORDER BY ID;
Is there a way to pass information onto the Delete trigger such that it could know who deleted the record?
Yes: by using a very cool (and under utilized feature) called CONTEXT_INFO
. It is essentially session memory that exists in all scopes and is not bound by transactions. It can be used to pass info (any info--well, any that fits into the limited space) to triggers as well as back and forth between sub-proc / EXEC calls. And I have used it before for this exact same situation.
Test with the following to see how it works. Notice that I am converting to CHAR(128)
before the CONVERT(VARBINARY(128), ..
. This is to force blank-padding to make it easier to convert back to VARCHAR
when getting it out of CONTEXT_INFO()
since VARBINARY(128)
is right-padded with 0x00
s.
SELECT CONTEXT_INFO();
-- Initially = NULL
DECLARE @EncodedUser VARBINARY(128);
SET @EncodedUser = CONVERT(VARBINARY(128),
CONVERT(CHAR(128), 'I deleted ALL your records! HA HA!')
);
SET CONTEXT_INFO @EncodedUser;
SELECT CONTEXT_INFO() AS [RawContextInfo],
RTRIM(CONVERT(VARCHAR(128), CONTEXT_INFO())) AS [DecodedUser];
Results:
0x492064656C6574656420414C4C20796F7572207265636F7264732120484120484121202020202020...
I deleted ALL your records! HA HA!
PUTTING IT ALL TOGETHER:
The app should call a "Delete" stored procedure that passes in the UserName (or whatever) that is deleting the record. I assume this is already the model being used since it sounds like you are already tracking Insert and Update operations.
The "Delete" stored procedure does:
DECLARE @EncodedUser VARBINARY(128);
SET @EncodedUser = CONVERT(VARBINARY(128),
CONVERT(CHAR(128), @UserName)
);
SET CONTEXT_INFO @EncodedUser;
-- DELETE STUFF HERE
The audit trigger does:
-- Set the INT value in LEFT (currently 50) to the max size of [UserWhoMadeChanges]
INSERT INTO AuditTable (IdOfRecordedAffected, UserWhoMadeChanges)
SELECT del.ID, COALESCE(
LEFT(RTRIM(CONVERT(VARCHAR(128), CONTEXT_INFO())), 50),
'<unknown>')
FROM DELETED del;
Please note that, as @SeanGallardy pointed out in a comment, due to other procedures and/or ad hoc queries deleting records from this table, it is possible that either:
CONTEXT_INFO
has not been set and is still NULL
:
For this reason I have updated the above INSERT INTO AuditTable
to use a COALESCE
to default the value. Or, if you don't want a default and require a name, then you could do something similar to:
DECLARE @UserName VARCHAR(50); -- set to the size of AuditTable.[UserWhoMadeChanges]
SET @UserName = LEFT(RTRIM(CONVERT(VARCHAR(128), CONTEXT_INFO())), 50);
IF (@UserName IS NULL)
BEGIN
ROLLBACK TRAN; -- cancel the DELETE operation
RAISERROR('Please set UserName via "SET CONTEXT_INFO.." and try again.', 16 ,1);
END;
-- use @UserName in the INSERT...SELECT
CONTEXT_INFO
has been set to a value that is not a valid UserName, and hence might exceed the size of the AuditTable.[UserWhoMadeChanges]
field:
For this reason I added a LEFT
function to ensure that whatever is grabbed out of CONTEXT_INFO
will not break the INSERT
. As noted in the code, you just need to set the 50
to the actual size of the UserWhoMadeChanges
field.
UPDATE FOR SQL SERVER 2016 AND NEWER
SQL Server 2016 added an improved version of this per-session memory: Session Context. The new Session Context is essentially a hash table of Key-Value pairs with the "Key" being of type sysname
(i.e. NVARCHAR(128)
) and the "Value" being SQL_VARIANT
. Meaning:
- There is now a separation of values so less likely to conflict with other uses
- You can store various types, no longer needing to worry about the odd behavior when getting the value back out via
CONTEXT_INFO()
(for details, please see my post: Why Doesn’t CONTEXT_INFO() Return the Exact Value Set by SET CONTEXT_INFO?)
- You get a lot more space: 8000 bytes max per "Value", up to 256kb total across all keys (compared to the 128 bytes max of
CONTEXT_INFO
)
For details, please see the following documentation pages:
Best Answer
Finding all nodes in a subtree to update them requires a recursive common table expression:
In SQLite, CTEs are not allowed in triggers.