Samuele E. Locatelli
41 lines
1.5 KiB
Transact-SQL
41 lines
1.5 KiB
Transact-SQL
CREATE TABLE [dbo].[MtzProgrammata] (
|
|
[idxIntPro] INT IDENTITY (1, 1) NOT NULL,
|
|
[idxMacchina] INT NULL,
|
|
[inizio] DATETIME NULL,
|
|
[codFrequenza] NCHAR (2) NULL,
|
|
[cadenza] INT NULL,
|
|
[descrizione] NVARCHAR (2500) NULL,
|
|
[idxPriorita] INT NULL,
|
|
[isFermo] BIT CONSTRAINT [DF_MtzProgrammata_isFermo] DEFAULT ((1)) NULL,
|
|
[idxTipo] INT NULL,
|
|
CONSTRAINT [PK_MtzProgrammata] PRIMARY KEY CLUSTERED ([idxIntPro] ASC),
|
|
CONSTRAINT [FK_MtzProgrammata_AnagFrequenze] FOREIGN KEY ([codFrequenza]) REFERENCES [dbo].[AnagFrequenze] ([codFrequenza]) ON UPDATE CASCADE,
|
|
CONSTRAINT [FK_MtzProgrammata_AnagMacchine] FOREIGN KEY ([idxMacchina]) REFERENCES [dbo].[AnagMacchine] ([idxMacchina]) ON UPDATE CASCADE
|
|
);
|
|
|
|
|
|
GO
|
|
/*********************************************************************************
|
|
* TRIGGER trg_updateMPPending
|
|
*
|
|
* eliminazione, in caso di update di un intervento di MtzProgrammata,
|
|
* dei record di interventi "pending" predisposti... così saranno rigenerati corretti
|
|
*
|
|
**********************************************************************************/
|
|
CREATE TRIGGER trg_updateMPPending
|
|
ON dbo.MtzProgrammata
|
|
FOR UPDATE, DELETE
|
|
AS
|
|
|
|
BEGIN tran
|
|
-- elimino interventi pending interessati...
|
|
DELETE
|
|
FROM MtzProgPending
|
|
WHERE idxIntPro IN
|
|
(
|
|
-- prendendo gli interventi mtzProgrammata modificati...
|
|
SELECT DISTINCT idxIntPro FROM DELETED
|
|
)
|
|
|
|
COMMIT tran
|