USE [Elyse_DB] GO /****** Object: StoredProcedure [controlling].[usp_DEL_tag_tree_perm_plist] Script Date: Wed 21-10-2026 4:50:16 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Silkwood Software -- Create date: 24-09-2024 -- Description: Initial creation -- Deletes a tag link permissions record to revoke authorisation for members of a people list to edit a tag tree -- Input and output parameters are as declared below. /* COPYRIGHT NOTICE This database schema and stored procedures are protected by copyright. Copyright. Silkwood Software Pty. Ltd. 2024 */ -- ============================================= CREATE PROCEDURE [controlling].[usp_DEL_tag_tree_perm_plist] @tag_treeid bigint = NULL, @peoplelistid bigint = NULL, @message nvarchar(1000) = '' OUTPUT, @transaction_status nvarchar(50) = NULL OUTPUT AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; DECLARE @connectedusersid varbinary(100) = SUSER_SID(ORIGINAL_LOGIN()), -- The SID of the connected user @username nvarchar(150) = ORIGINAL_LOGIN(), -- The username @userauthentication_status nchar(10) = 'Fail', -- The outcome of the authentication check of the user @validationmessage nvarchar(200) = '', -- Communicates that the transaction failed due to data validation @tempuserauth_status nchar(10) = '', @tempmessage nvarchar(300) = '', @tempvalidationstatus nchar(10) = '', @transaction_ready nchar(10) = 'Ready', @data_validation_status nchar(10) = 'Pass'; -- Parameters which have been initialised at declaration but not explicitly set might be output as null to calling functions. SET @transaction_status = 'Transaction not attempted'; -- Connected user authentication -- Authenticate the connected user for the role EXEC [internal].[usp_AUTHENTICATE_user_role] @role_to_check = 'Controller', @user_authentication_result = @userauthentication_status OUTPUT; IF @userauthentication_status = 'Fail' BEGIN -- The user does not have permission for this action SET @transaction_ready = 'Fail'; EXEC internal.usp_SEL_message @message_id = 'NoPermission', @message_text = @tempmessage OUTPUT; IF (@tempmessage IS NOT NULL) SET @message = CONCAT_WS(' | ', @message, ISNULL(@username, ''), ' ', @tempmessage); ELSE SET @message = CONCAT_WS(' | ', @message, 'A database level message error occurred on NoPermission. '); END IF @userauthentication_status = 'Pass' -- Don't do anything if the user is not authorised. BEGIN IF @tag_treeid = 0 SET @tag_treeid = NULL; -- Check if a tag tree id was supplied. IF @tag_treeid IS NULL BEGIN -- A document multi-select attribute id has not been supplied. SET @data_validation_status = 'Fail'; SET @transaction_ready = 'Fail'; EXEC internal.usp_SEL_message @message_id = 'NoTagTree', @message_text = @tempmessage OUTPUT; IF (@tempmessage IS NOT NULL) SET @message = CONCAT_WS(' | ', @message, @tempmessage); ELSE SET @message = CONCAT_WS(' | ', @message, 'A database level message error occurred on NoTagTree.'); END IF @tag_treeid IS NOT NULL AND @data_validation_status = 'Pass' BEGIN IF NOT EXISTS (SELECT tag_tree_id FROM tagging.tag_tree_names WHERE tag_tree_id = @tag_treeid) BEGIN -- tag tree id does not exist SET @data_validation_status = 'Fail'; SET @transaction_ready = 'Fail'; EXEC internal.usp_SEL_message @message_id = 'TagTreeNotExist', @message_text = @tempmessage OUTPUT; IF (@tempmessage IS NOT NULL) SET @message = CONCAT_WS(' | ', @message, ISNULL(CONVERT(nvarchar(10), @tag_treeid), 'NULL'), @tempmessage); ELSE SET @message = CONCAT_WS(' | ', @message, 'A database level message error occurred on TagTreeNotExist.'); END END -- Check the people list id IF @peoplelistid = 0 SET @peoplelistid = NULL; IF @peoplelistid IS NULL BEGIN EXEC internal.usp_SEL_message @message_id = 'NoPeopleListID', @message_text = @tempmessage OUTPUT; IF (@tempmessage IS NOT NULL) SET @message = CONCAT_WS(' | ', @message, @tempmessage); ELSE SET @message = CONCAT_WS(' | ', @message, 'A database level message error occurred on NoPeopleListID'); SET @data_validation_status = 'Fail'; SET @transaction_ready = 'Fail'; END ELSE -- People list has been supplied. Check if it is valid BEGIN IF NOT EXISTS (SELECT i.people_list_id FROM people.people_list_names AS i WHERE i.people_list_id = @peoplelistid) BEGIN SET @data_validation_status = 'Fail'; SET @transaction_ready = 'Fail'; EXEC internal.usp_SEL_message @message_id = 'PeopleListIDNotExist', @message_text = @tempmessage OUTPUT; IF (@tempmessage IS NOT NULL) SET @message = CONCAT_WS(' | ', @message, ISNULL(CONVERT(nvarchar(10), @peoplelistid), 'NULL'), @tempmessage); ELSE SET @message = CONCAT_WS(' | ', @message, 'A database level message error occurred on PeopleListIDNotExist.'); END END -- Check if the record exists IF @data_validation_status = 'Pass' BEGIN IF NOT EXISTS (SELECT tag_tree_id FROM user_restr.tag_tree_permissions_plist WHERE tag_tree_id = @tag_treeid AND people_list_id = @peoplelistid) BEGIN SET @data_validation_status = 'Fail'; SET @transaction_ready = 'Fail'; EXEC internal.usp_SEL_message @message_id = 'NotExist', @message_text = @tempmessage OUTPUT; IF (@tempmessage IS NOT NULL) SET @message = CONCAT_WS(' | ', @message, @tempmessage); ELSE SET @message = CONCAT_WS(' | ', @message, 'A database level message error occurred on NotExist.'); END END -- Output the data validation status failed message IF @data_validation_status = 'Fail' BEGIN EXEC internal.usp_SEL_message @message_id = 'FailedDataValidation', @message_text = @tempmessage OUTPUT; IF (@tempmessage IS NOT NULL) SET @message = CONCAT_WS(' | ', @message, @tempmessage); ELSE SET @message = CONCAT_WS(' | ', @message, 'A database level message error occurred on FailedDataValidation.'); END -- End data validation END -- End user authentication = Pass -- Execute the delete query IF @transaction_ready = 'Ready' BEGIN BEGIN TRY BEGIN TRANSACTION; DELETE user_restr.tag_tree_permissions_plist WHERE tag_tree_id = @tag_treeid AND people_list_id = @peoplelistid; COMMIT TRANSACTION; EXEC internal.usp_SEL_message @message_id = 'Success', @message_text = @tempmessage OUTPUT; IF (@tempmessage IS NOT NULL) SET @message = CONCAT_WS(' | ', @message, @tempmessage); ELSE SET @message = CONCAT_WS(' | ', @message, 'A database level message error occurred on Success.'); SET @transaction_status = 'Good'; END TRY BEGIN CATCH IF XACT_STATE() <> 0 ROLLBACK TRANSACTION; SET @transaction_status = 'Bad'; EXEC internal.usp_SEL_message @message_id = 'DeleteError', @message_text = @tempmessage OUTPUT; IF (@tempmessage IS NOT NULL) SET @message = CONCAT(@message, @tempmessage, ' | ', CONVERT(nvarchar(10),ERROR_NUMBER()), ' | ', ERROR_MESSAGE()); ELSE SET @message = CONCAT_WS(' | ', @message, 'A database level message error occurred on DeleteError.'); END CATCH END END GO