Status History, Sql Server

Status History

-- =========== EXISTING ===========
-- Status list definition
CREATE TABLE t_status (
  code int IDENTITY(1,1) PRIMARY KEY,
  name nvarchar(32)
);

INSERT INTO t_status (name)
VALUES ('A'), ('B'), ('C'), ('D'), ('E');

-- Messages queue definition
CREATE TABLE t_queue (
  id bigint IDENTITY(1,1) PRIMARY KEY,
  status_code int FOREIGN KEY REFERENCES t_status (code) DEFAULT 1,
  message_txt nvarchar(1024)
);

-- ======================== ADDING ========================
-- STATUS HISTORY
CREATE TABLE StatusHistory (
  ChangeID bigint IDENTITY(1,1) PRIMARY KEY NOT NULL,
  [Message] bigint FOREIGN KEY REFERENCES t_queue (id) NOT NULL,
  [Status] int NOT NULL,
  [Assigned] datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
);

GO
CREATE TRIGGER tr_Queue_InsertOrUpdateStatus
ON t_queue
AFTER INSERT, UPDATE
AS
INSERT INTO StatusHistory ([Message], [Status])
SELECT i.id, i.status_code
FROM inserted i LEFT JOIN deleted d ON i.id = d.id
WHERE ISNULL(i.status_code, -1000) <> ISNULL(d.status_code, -1000);

-- Fill the queue. It must be included to the history.
GO
INSERT INTO t_queue (message_txt)
VALUES ('First message'), ('Second message'), ('Message 3'), ('Message 4'), ('Message 5');

-- Update statuses of all messages. It must be included to the history.
UPDATE t_queue
SET status_code = 2;

-- Update a message text. The changes must be ignored in the history.
UPDATE t_queue
SET message_txt = 'Message 1'
WHERE message_txt LIKE '%First%';

UPDATE t_queue
SET message_txt = 'Message 2'
WHERE message_txt LIKE '%Second%';

-- It does not change status and must be ignored in the history.
UPDATE t_queue
SET status_code = 2
WHERE message_txt LIKE '%3%';

-- Update a message status. It must be included to the history.
UPDATE t_queue
SET status_code = 3
WHERE message_txt LIKE '%4%';

UPDATE t_queue
SET status_code = 4
WHERE message_txt LIKE '%1%' OR message_txt LIKE '%4%';

-- Show the history of statuses.
SELECT
    h.[Assigned],
    q.message_txt,
    s.name [status]
FROM
    StatusHistory h
LEFT JOIN
    t_queue q ON h.[Message] = q.id
LEFT JOIN
    t_status s ON h.[Status] = s.code
ORDER BY h.ChangeID;


run \| edit \| history \| help	0