CDR Tickets

Issue Number 5112
Summary Prune obsolete stored procedures
Created 2022-05-24 16:04:42
Issue Type Improvement
Submitted By Kline, Bob (NIH/NCI) [C]
Assigned To Kline, Bob (NIH/NCI) [C]
Status Closed
Resolved 2022-06-08 17:09:35
Resolution Fixed
Path /home/bkline/backups/jira/ocecdr/issue.318942
Description

As part of the SQL Server upgrade, we can drop some of the stored procedures, which are no longer used (mostly because they involve protocol documents, which we no longer have).

Comment entered 2022-05-25 10:28:32 by Kline, Bob (NIH/NCI) [C]

Also need to add missing trigger, which seems to have disappeared on all tiers.

/*
 * Make sure a CDR document is not marked as 'D'eleted if there's a row
 * in the external_map table which maps to it.
 */
CREATE TRIGGER cdr_mod_doc ON all_docs
FOR UPDATE
AS
    IF UPDATE(active_status)
    BEGIN
        IF EXISTS (SELECT i.id
                     FROM cdr..external_map m
                     JOIN inserted i
                       ON m.doc_id = i.id
                    WHERE i.active_status = 'D')
        BEGIN
            RAISERROR('Attempt to delete document in external_map table', 16, 1)
            ROLLBACK TRANSACTION
        END
    END
GO
Comment entered 2022-06-08 17:09:35 by Kline, Bob (NIH/NCI) [C]

Done on CDR DEV.

Comment entered 2022-10-11 06:57:41 by Kline, Bob (NIH/NCI) [C]

I'm still testing this ticket.

Comment entered 2022-10-11 09:03:33 by Kline, Bob (NIH/NCI) [C]

Tested by switching the active_status value from 'A' to 'I' for a document linked from the external_map table. This action was allowed, appropriately. Then I attempted to set the status to 'D' for that document, and that action was blocked, as it was supposed to be. I then restored the status to 'A' (which was also successful). I then verified that all of the obsolete stored procedures were removed.

Elapsed: 0:00:00.001337