Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
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
BC160401693
top 3 salaries from each dept
sc
Генерация чисел в диапазоне в MS SQL
sql-server-storing-wildcards-in-data
Outer Apply successer cell chek and get the result
pivot example
Remove duplicates with in 10 min
sql server dynamic 12 month time series and pivot with month year column names
Ranking