Samuele Locatelli
37 lines
1.4 KiB
Transact-SQL
37 lines
1.4 KiB
Transact-SQL
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 |