CREATE TABLE [dbo].[Dtx2UDC] (
    [DataMatrix]  NVARCHAR (50) NOT NULL,
    [CodStato]    NVARCHAR (50) CONSTRAINT [DF_Dtx2UDC_CodStato] DEFAULT ('RX') NOT NULL,
    [UDC]         NVARCHAR (50) CONSTRAINT [DF_Dtx2UDC_UDC] DEFAULT ('') NOT NULL,
    [DataOra]     DATETIME      CONSTRAINT [DF_Dtx2UDC_DataOra] DEFAULT (getdate()) NOT NULL,
    [CodSoggetto] NCHAR (17)    CONSTRAINT [DF_Dtx2UDC_CodSoggetto] DEFAULT ('') NOT NULL,
    [Particolare] NVARCHAR (15) CONSTRAINT [DF_Dtx2UDC_Particolare] DEFAULT ('') NULL,
    CONSTRAINT [PK_Dtx2UDC] PRIMARY KEY CLUSTERED ([DataMatrix] ASC, [CodStato] ASC),
    CONSTRAINT [FK_Dtx2UDC_ElencoCartellini] FOREIGN KEY ([UDC]) REFERENCES [dbo].[ElencoCartellini] ([UDC]) ON UPDATE CASCADE
);




GO
-- =============================================
-- Author:		Samuele E. Locatelli
-- Create date: 2009-10-27
-- Description:	trigger x inserimento valori in archivio storico alla cancellazione/modifica del dato
-- =============================================
CREATE TRIGGER [dbo].[trg_storicoDtx] 
   ON  dbo.Dtx2UDC 
   AFTER DELETE,UPDATE
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- metto in storico i dati
	INSERT INTO StoricoDtx2UDC(DataEv, DataMatrix, CodStato, UDC, DataOra, CodSoggetto, Particolare)
	(
		SELECT		GETDATE(), DataMatrix, CodStato, UDC, DataOra, CodSoggetto, Particolare
		FROM	deleted
	)

END