CREATE TABLE [dbo].[RigheListePrelievo] (
    [CodLista]  NVARCHAR (12)   NOT NULL,
    [UDC]       NVARCHAR (50)   NOT NULL,
    [Qta]       DECIMAL (10, 2) NOT NULL,
    [Proposto]  BIT             NULL,
    [Prelevato] BIT             NULL,
    CONSTRAINT [PK_RigheListePrelievo] PRIMARY KEY CLUSTERED ([CodLista] ASC, [UDC] ASC) WITH (FILLFACTOR = 95),
    CONSTRAINT [FK_RigheListePrelievo_ElencoCartellini] FOREIGN KEY ([UDC]) REFERENCES [dbo].[ElencoCartellini] ([UDC]) ON UPDATE CASCADE,
    CONSTRAINT [FK_RigheListePrelievo_ElencoListePrelievo1] FOREIGN KEY ([CodLista]) REFERENCES [dbo].[ElencoListePrelievo] ([CodLista]) ON UPDATE CASCADE
);


GO
CREATE NONCLUSTERED INDEX [ix_RigheListePrelievo_UDC_Prelevato]
    ON [dbo].[RigheListePrelievo]([UDC] ASC, [Prelevato] ASC)
    INCLUDE([Qta]) WITH (FILLFACTOR = 95);


GO
CREATE NONCLUSTERED INDEX [IX_RigheListePrelievo_UDC]
    ON [dbo].[RigheListePrelievo]([UDC] ASC) WITH (FILLFACTOR = 95);


GO
CREATE NONCLUSTERED INDEX [IX_RigheListePrelievo_NON_Prelevate]
    ON [dbo].[RigheListePrelievo]([Prelevato] ASC)
    INCLUDE([CodLista], [Qta]) WHERE (Prelevato=(0));


GO


-- =============================================
-- Author:		Samuele E. Locatelli
-- Create date: 2011-05-20
-- Description:	trigger x inserimento in tab
-- storico record cancellati
-- =============================================
CREATE TRIGGER [dbo].[trg_RigheListePrelievoDel]
   ON [dbo].[RigheListePrelievo]
   AFTER DELETE
AS 
BEGIN

    -- setup variabili e verbosità
    SET NOCOUNT ON;
    -- inserisco in storico
    INSERT INTO RigheListePrelievo_storico 
        (DataOraDelete,CodLista,UDC,Qta,Proposto,Prelevato)
    
    SELECT	GETDATE(), CodLista,UDC,Qta,Proposto,Prelevato
    FROM	deleted
    

END

