----------------------
-- Visit_Add_tr.sql --
----------------------
--
-- This is an example stored proc that conforms to the OLTP Standard (template),
-- as described in the Open Architecture Standard:
--     https://www.softwaregems.com.au/Documents/Article/Application%20Architecture/Open%20Architecture.pdf
--
-- As such, it implements:
--     Optimistic Locking      - IBM 1965 definition
--     Prevents Lost Updates   - maintains ACID [D]urable
--     Preven Lost Currency    - maintains ACID [C]onsistent
--     Maintains ACID [A]tomic - BEGIN TRAN..COMMIT in a contiguous code block
--     Maintains the proper SQL ACID Isolation Level
--
-- Original:
--     Daniel Loth 2 Aug 21
--
-- Upgraded to OLTP Standard:
--     Derek Ignatius Asirvadem 12 Aug 21
--
-- Documentation:
--     https://www.softwaregems.com.au/Documents/Article/Database/Transaction/Transaction%20Sanity.pdf
--
-- Article (voluminous due to discussion & stepwise education):
--     https://groups.google.com/g/comp.databases.theory/c/BNL-TwgMfPY
--
-- Database (for DDL) (DML is obsolete, this file is the latest version):
--     https://gist.github.com/DanielLoth/76d241515655e76cadddef6ed2d373aa
--
-- Syntax:
--     Generic: ISO/IEC SQL (freeware & Oracle does not provide SQL or ACID)
--     Specific Parms & Err msgs: MS SQL Server & almost SAP/Sybase (Transact-SQL)
--

CREATE PROC Visit_Add_tr (
    @OrganisationId     INT,        -- Organisation Key
    @PersonId           INT,        -- Person Key
    @UpdatedDtm         DATETIME,   -- Person row Currency (not retrieval DateTime)
    @VisitDate          DATE        -- Date of range visit
    )
AS

DECLARE @ProcName SYSNAME = OBJECT_NAME(@@procid)

--------------------------
-- Xact Integrity Check --
--------------------------

-- Ensure we are not running in an already-opened transaction.
IF @@TRANCOUNT > 0              -- Transaction depth
    BEGIN
    RAISERROR (100001, -1, -1, @ProcName)
    RETURN 1
    END

-- Ensure we are not running in 'implicit transactions' mode
-- (also known as 'chained transactions' or 'autocommit' on some platforms)
IF @@OPTIONS & 2 != 0           -- @@TRANCHAINED = 1 for Saybase
    BEGIN
    RAISERROR (100003, -1, -1, @ProcName)
    RETURN 1
    END

----------------------
-- Validation Block --
----------------------

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
    -- This is still outside the Transaction
    -- No locks held (excecpt of course, during Read operations)

IF NOT EXISTS (                 -- Check Organisation exists
        SELECT 1
        FROM  Organisation
        WHERE OrganisationId = @OrganisationId
        )
    BEGIN
    RAISERROR (100100, -1, -1, @ProcName) -- Organisation does not exist
    RETURN 1
    END

IF NOT EXISTS (                 -- Check Person Currency
        SELECT 1
            FROM  Person
            WHERE OrganisationId = @OrganisationId
            AND   PersonId       = @PersonId
            AND   UpdatedDtm     = @UpdatedDtm
        )
    BEGIN
    -- Required only to produce separate err msgs, from a full list
    -- /Not required in standard template/
    IF NOT EXISTS (             -- Check Person Existence
            SELECT 1
                FROM  Person
                WHERE OrganisationId = @OrganisationId
                AND   PersonId       = @PersonId
            )
        BEGIN
        RAISERROR (100200, -1, -1, @ProcName) -- Person does not exist
        RETURN 1
        END
    ELSE
        BEGIN
        RAISERROR (100201, -1, -1, @ProcName) -- Person Currency is lost
        RETURN 1
        END

IF EXISTS (                     -- Check Visit Not Exists
        SELECT 1
            FROM Visit
            WHERE OrganisationId = @OrganisationId
            AND   PersonId       = @PersonId
            AND   VisitDate      = @VisitDate
        )
    BEGIN
    RAISERROR (100301, -1, -1, @ProcName) -- Visit on the given date has already been recorded.
    RETURN 1
    END

-------------------
-- Execute block --
-------------------

BEGIN TRANSACTION Visit_Add     -- Transaction begins here
                                -- Isolation Level 1
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
                                -- Now holding locks, to end of tran

IF NOT EXISTS (                 -- Check Organisation exists (already in cache)
        SELECT 1
            FROM  Organisation
            WHERE OrganisationId = @OrganisationId
        )
    BEGIN
    ROLLBACK Visit_Add
    RAISERROR (100100, -1, -1, @ProcName) -- Organisation does not exist
    RETURN 1
    END

SELECT 1                        -- Check Person Currency (no need to check existence)
    FROM  Person
    WHERE OrganisationId = @OrganisationId
    AND   PersonId       = @PersonId
    AND   UpdatedDtm     = @UpdatedDtm

IF (@@ROWCOUNT != 1)
    BEGIN
    ROLLBACK Visit_Add
    RAISERROR (100201, -1, -1, @ProcName) -- Person has been updated by another user
    RETURN 1
    END

IF EXISTS (
    SELECT 1
        FROM  Visit
        WHERE OrganisationId = @OrganisationId
        AND   PersonId       = @PersonId
        AND   VisitDate      = @VisitDate
    )
    BEGIN
    ROLLBACK Visit_Add
    RAISERROR (100301, -1, -1, @ProcName) -- Visit on the given date has already been recorded.
    RETURN 1
    END

UPDATE Person                       -- Update TimeStamp in Person
    SET   UpdatedDtm = GETDATE()    -- whatever the server is set to, local, not UTC
    WHERE OrganisationId = @OrganisationId
    AND   PersonId = @PersonId

IF (@@ERROR OR @@ROWCOUNT != 1)
    BEGIN
    ROLLBACK Visit_Add
    RAISERROR (999, -1, -1, @ProcName) -- Person update failed with @@ERROR
    RETURN 1
    END

INSERT INTO Visit (
        OrganisationId,
        PersonId,
        VisitDate
        )
    VALUES (
        @OrganisationId,
        @PersonId,
        @VisitDate
        )

IF (@@ERROR OR @ROWCOUNT != 1)
    BEGIN
    ROLLBACK Visit_Add
    RAISERROR (999, -1, -1, @ProcName) -- Insert Visit failed with @@ERROR
    RETURN 1
    END

------------
-- Commit --
------------

COMMIT TRAN Visit_Add

RETURN 0
