Table logic not working

Transact-SQL
1

as per my requirement for each UID entered [Stage ID] should the fallow the sequence IDEN,PREP,VALI .
but in the above data STAGE ID Seqence for AA0001 ID is PREP,VALI,IDEN

can any one please help me on the below scenario.

create table allocation (
[UID] varchar(10) not null ,
[EMP ID] INT not null foreign key references ASSOCIATES([EMP ID]),
[Stage ID] varchar(10) not null foreign key references [Stage Description]([Stage ID]),
[Date of Assign] date not null default (getdate()),
primary key ([uid],[emp id])
)

2

Data stored in a table has no order to it. And, it does not matter what order it is stored (at least for the purposes of this discussion). What matters is how the data is ordered when you retrieve it - which of course is via a SELECT statement. So if you want the data to be ordered as you indicated, you can do this:

SELECT * FROM allocation
ORDER BY
   UID,
   CASE 
      WHEN [STAGE ID] = 'iden' THEN 0
      WHEN [STAGE ID] = 'prep' THEN 1
      WHEN [STAGE ID] = 'vali' THEN 2
   END
3

hi,

thanks for response.

my intention is keep restriction while inserting data , not while retrieve.

CREATE PROC ALLOC
@UID CHAR(6),
@EMP_ID INT,
@STAGE_ID varchar(10),
@DATE_OF_ASSIGN DATE
AS
IF EXISTS (SELECT 'TRUE' FROM allocation WHERE UID=@UID AND [stage id]=@STAGE_ID)
begin
SELECT 'Wrong allocation pls check UID and Stage ID'
end
else
begin
SELECT 'Record Added'
INSERT INTO allocation([UID],[EMP ID],[Stage ID],[DATE OF ASSIGN]) values(@UID,@EMP_ID ,@STAGE_ID,@DATE_OF_ASSIGN)
END

4

In relational database theory, a relation (generally know as a table) is by definition an UNordered set.

5

THANKS.

BUT WHILE INSERTING DATA IT SHOULD THROW ERROR THAT PREVIOUS STAGE IS NOE ASSIGNED FOR THE UID.

CREATE PROC ALLOC
@UID CHAR(6),
@EMP_ID INT,
@STAGE_ID varchar(10),
@DATE_OF_ASSIGN DATE
AS
IF EXISTS (SELECT 'TRUE' FROM allocation WHERE UID=@UID AND [stage id]=@STAGE_ID)
begin
SELECT 'Wrong allocation pls check UID and Stage ID'
end
else
begin
SELECT 'Record Added'
INSERT INTO allocation([UID],[EMP ID],[Stage ID],[DATE OF ASSIGN]) values(@UID,@EMP_ID ,@STAGE_ID,@DATE_OF_ASSIGN)
END

6

Something like the following should stop future problems:

WITH AllocationStatuses
AS
(
    SELECT *
    FROM
    (
        VALUES ('iden', 1),('prep', 2), ('vali', 3)
    ) V (Stage_ID, StageValue)
)
INSERT INTO allocation([UID],[EMP ID],[Stage ID],[DATE OF ASSIGN])
SELECT @UID,@EMP_ID ,@STAGE_ID,@DATE_OF_ASSIGN
FROM AllocationStatuses S
WHERE S.Stage_ID = @STAGE_ID
    AND S.StageValue >
    (
        SELECT COALESCE(MAX(S1.StageValue), 0) AS StageValue
        FROM Allocation A WITH (UPDLOCK, SERIALIZABLE)
            JOIN AllocationStatuses S1
                ON A.[Stage ID] = S1.Stage_ID
        WHERE A.[UID] = @UID
    );

IF @@ROWCOUNT() = 0
BEGIN
    -- handle error
END
7

To show problems with the current data, try something like:

WITH AssignOrder
AS
(
    SELECT [UID],[EMP ID],[Stage ID],[DATE OF ASSIGN]
        ,ROW_NUMBER() OVER (PARTITION BY [UID] ORDER BY [DATE OF ASSIGN]) AS AO
    FROM Allocation
)
,AllocationStatuses
AS
(
    SELECT *
    FROM
    (
        VALUES ('iden', 1),('prep', 2), ('vali', 3)
    ) V (Stage_ID, StageValue)
)
SELECT *
FROM  AssignOrder A
    JOIN AllocationStatuses S
        ON A.[Stage ID] = S.Stage_ID
WHERE A.AO <> S.StageValue;
8
  1. You must use a database trigger to do that consistently and accurately.
  2. The "STAGE ID" value should be encoded to numeric, such as a tinyint, not char. You would translate the char to numeric prior to display or upon entry. But, since that would require other code changes, I'll ignore it for now.

I don't have time to code the trigger now but will come back later if/when I do, unless someone else has coded it in the meantime.

9

Good point. Assuming [Stage ID] is never modified, maybe something like:

SET ANSI_NULLS, QUOTED_IDENTIFIER ON
GO
CREATE TABLE dbo.AllocationStatuses
(
    Stage_ID varchar(20) NOT NULL
        CONSTRAINT PK_AllocationStatuses PRIMARY KEY
    ,StageValue tinyint NOT NULL
);
INSERT INTO dbo.AllocationStatuses
VALUES ('iden', 1),('prep', 2), ('vali', 3);
GO
CREATE TRIGGER TR_I_Allocation ON dbo.Allocation
AFTER INSERT
AS
BEGIN;
    SET NOCOUNT ON;
    IF NOT EXISTS
    (    

        SELECT 1
        FROM inserted I
            JOIN dbo.AllocationStatuses S
                ON I.[Stage ID] = S.Stage_ID
        WHERE S.StageValue >
            (
                SELECT COALESCE(MAX(S1.StageValue), 0) AS StageValue
                FROM Allocation A
                    JOIN AllocationStatuses S1
                        ON A.[Stage ID] = S1.Stage_ID
                WHERE A.[UID] = I.UID
                    AND A.[Stage ID] <> I.[Stage ID]
            )
    )
    BEGIN;
        ROLLBACK;
        RAISERROR('Invalid Stage_ID order in table Allocation', 16, 1);
    END;
END;