Life cycle, Sql Server

Life cycle

CREATE TABLE DiscountCampaignState(
	[StateID] [int] NOT NULL IDENTITY(1,1),
	[Name] [nvarchar](100) NOT NULL,
	[Descr] [nvarchar](100) NULL,
	[dtUpdate] [datetime] NOT NULL,
 CONSTRAINT [PK_DISCOUNTCAMPAIGNSTATE] PRIMARY KEY CLUSTERED 
(
	[StateID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE DiscountCampaignState ADD  DEFAULT (getdate()) FOR [dtUpdate]
GO

INSERT INTO DiscountCampaignState
           ([Name]           ,[Descr])
     VALUES
           ('Активна', 'Правила проведения акции применяются в соответствии с определёнными ими условиями.'),
           ('Завершена', 'Акция завершена по причине истечения срока её действия.'),
           ('Прервана', 'Акция прервана в процессе её выполнения.'),
           ('Удалена', 'Акция удалена. Правила её проведения не действуют.'),
           ('Планирование', 'Формулируются правила проведения акции.'),
           ('Тестирование', 'Проверка работоспособности правил проведения акции.'),
           ('Ожидание', 'Акция утверждена, ожидается её проведение в магазинах.')
GO

ALTER TABLE DiscountCampaignState ADD
		Mnemonics VARCHAR(10) NULL,
		Editable BIT CONSTRAINT DF_Editable DEFAULT 1;

GO
	UPDATE DiscountCampaignState SET Mnemonics = 'ACTIVE' WHERE StateID = 1;
	UPDATE DiscountCampaignState SET Mnemonics = 'COMPLETED' WHERE StateID = 2;
	UPDATE DiscountCampaignState SET Mnemonics = 'ABORTED' WHERE StateID = 3;
	UPDATE DiscountCampaignState SET Mnemonics = 'REMOVED' WHERE StateID = 4;
	UPDATE DiscountCampaignState SET Mnemonics = 'PLAN' WHERE StateID = 5;
	UPDATE DiscountCampaignState SET Mnemonics = 'TEST' WHERE StateID = 6;
	UPDATE DiscountCampaignState SET Mnemonics = 'WAIT' WHERE StateID = 7;
	UPDATE DiscountCampaignState SET Editable = 1;
    
	ALTER TABLE DiscountCampaignState
		ALTER COLUMN Mnemonics VARCHAR(10) NOT NULL;
	ALTER TABLE DiscountCampaignState
		ADD CONSTRAINT AK_Mnemonics UNIQUE(Mnemonics);
        
GO

-- Создаёт таблицу маршрутов
	CREATE TABLE DiscountCampaignStateRoute(
		RouteID INT NOT NULL IDENTITY(1,1), 
		[Current] INT NOT NULL,
		[Next] INT NOT NULL,
		LockBeforeStarted BIT DEFAULT 1,
		LockBeforeFinished BIT DEFAULT 1,
		CONSTRAINT PK_RouteID PRIMARY KEY CLUSTERED (RouteID),
		CONSTRAINT FK_Current_DiscountCampaignState FOREIGN KEY ([Current]) 
			REFERENCES DiscountCampaignState(StateID)
			ON DELETE NO ACTION
			ON UPDATE NO ACTION,
		CONSTRAINT FK_Next_DiscountCampaignState FOREIGN KEY ([Next]) 
			REFERENCES DiscountCampaignState(StateID)
			ON DELETE NO ACTION
			ON UPDATE NO ACTION
	);

GO
	-- Каскадное удаление
	CREATE TRIGGER tr_DiscountCampaignState_Delete
		ON DiscountCampaignState 
		INSTEAD OF DELETE
	AS
		DELETE DiscountCampaignStateRoute
		FROM DiscountCampaignStateRoute r
		INNER JOIN deleted d
		ON r.[Current] = d.StateID OR r.[Next] = d.StateID;
	    
		DELETE DiscountCampaignState
		FROM DiscountCampaignState s
		INNER JOIN deleted d
		ON s.StateID = d.StateID;
	GO

-- Добавляет маршруты
    INSERT INTO DiscountCampaignStateRoute([Current], [Next])
    SELECT c.StateID AS [Current], n.StateID AS [Next]
    FROM DiscountCampaignState c, DiscountCampaignState n
    WHERE 
        c.Mnemonics = n.Mnemonics OR
        (c.Mnemonics = 'PLAN' AND n.Mnemonics IN ('TEST', 'REMOVED')) OR
        (c.Mnemonics = 'TEST' AND n.Mnemonics IN ('PLAN', 'WAIT', 'REMOVED')) OR
        (c.Mnemonics = 'WAIT' AND n.Mnemonics IN ('PLAN', 'REMOVED', 'ACTIVE')) OR
        (c.Mnemonics = 'ACTIVE' AND n.Mnemonics IN ('ABORTED', 'COMPLETED'))
    
    GO

SELECT * FROM DiscountCampaignState;
SELECT * FROM DiscountCampaignStateRoute;


run \| edit \| history \| help	0