Samuele Locatelli
140 lines
10 KiB
Transact-SQL
140 lines
10 KiB
Transact-SQL
CREATE TABLE [dbo].[Odette] (
|
|
[CampoUDC] NVARCHAR (50) NOT NULL,
|
|
[Campo1_1] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo1_1] DEFAULT ('') NOT NULL,
|
|
[Campo1_2] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo1_2] DEFAULT ('') NOT NULL,
|
|
[Campo1_3] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo1_3] DEFAULT ('') NOT NULL,
|
|
[Campo1_4] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo1_4] DEFAULT ('') NOT NULL,
|
|
[Campo1_5] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo1_5] DEFAULT ('') NOT NULL,
|
|
[Campo2_1] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo2_1] DEFAULT ('') NOT NULL,
|
|
[Campo2_2] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo2_2] DEFAULT ('') NOT NULL,
|
|
[Campo2_3] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo2_3] DEFAULT ('') NOT NULL,
|
|
[Campo2_4] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo2_4] DEFAULT ('') NOT NULL,
|
|
[Campo2_5] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo2_5] DEFAULT ('') NOT NULL,
|
|
[Campo3_1] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo3_1] DEFAULT ('') NOT NULL,
|
|
[Campo3_2] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo3_2] DEFAULT ('') NOT NULL,
|
|
[Campo3_3] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo3_3] DEFAULT ('') NOT NULL,
|
|
[Campo3_4] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo3_4] DEFAULT ('') NOT NULL,
|
|
[Campo3_5] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo3_5] DEFAULT ('') NOT NULL,
|
|
[Campo4_1] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo4_1] DEFAULT ('') NOT NULL,
|
|
[Campo4_2] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo4_2] DEFAULT ('') NOT NULL,
|
|
[Campo4_3] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo4_3] DEFAULT ('') NOT NULL,
|
|
[Campo4_4] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo4_4] DEFAULT ('') NOT NULL,
|
|
[Campo4_5] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo4_5] DEFAULT ('') NOT NULL,
|
|
[Campo5_1] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo5_1] DEFAULT ('') NOT NULL,
|
|
[Campo5_2] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo5_2] DEFAULT ('') NOT NULL,
|
|
[Campo5_3] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo5_3] DEFAULT ('') NOT NULL,
|
|
[Campo5_4] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo5_4] DEFAULT ('') NOT NULL,
|
|
[Campo5_5] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo5_5] DEFAULT ('') NOT NULL,
|
|
[Campo6_1] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo6_1] DEFAULT ('') NOT NULL,
|
|
[Campo6_2] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo6_2] DEFAULT ('') NOT NULL,
|
|
[Campo6_3] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo6_3] DEFAULT ('') NOT NULL,
|
|
[Campo6_4] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo6_4] DEFAULT ('') NOT NULL,
|
|
[Campo6_5] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo6_5] DEFAULT ('') NOT NULL,
|
|
[Campo7_1] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo7_1] DEFAULT ('') NOT NULL,
|
|
[Campo7_2] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo7_2] DEFAULT ('') NOT NULL,
|
|
[Campo7_3] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo7_3] DEFAULT ('') NOT NULL,
|
|
[Campo7_4] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo7_4] DEFAULT ('') NOT NULL,
|
|
[Campo7_5] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo7_5] DEFAULT ('') NOT NULL,
|
|
[Campo8_1] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo8_1] DEFAULT ('') NOT NULL,
|
|
[Campo8_2] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo8_2] DEFAULT ('') NOT NULL,
|
|
[Campo8_3] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo8_3] DEFAULT ('') NOT NULL,
|
|
[Campo8_4] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo8_4] DEFAULT ('') NOT NULL,
|
|
[Campo8_5] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo8_5] DEFAULT ('') NOT NULL,
|
|
[Campo9_1] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo9_1] DEFAULT ('') NOT NULL,
|
|
[Campo9_2] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo9_2] DEFAULT ('') NOT NULL,
|
|
[Campo9_3] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo9_3] DEFAULT ('') NOT NULL,
|
|
[Campo9_4] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo9_4] DEFAULT ('') NOT NULL,
|
|
[Campo9_5] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo9_5] DEFAULT ('') NOT NULL,
|
|
[Campo10_1] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo10_1] DEFAULT ('') NOT NULL,
|
|
[Campo10_2] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo10_2] DEFAULT ('') NOT NULL,
|
|
[Campo10_3] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo10_3] DEFAULT ('') NOT NULL,
|
|
[Campo10_4] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo10_4] DEFAULT ('') NOT NULL,
|
|
[Campo10_5] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo10_5] DEFAULT ('') NOT NULL,
|
|
[Campo11_1_1] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo11_1_1] DEFAULT ('') NOT NULL,
|
|
[Campo11_1_2] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo11_1_2] DEFAULT ('') NOT NULL,
|
|
[Campo11_1_3] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo11_1_3] DEFAULT ('') NOT NULL,
|
|
[Campo11_1_4] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo11_1_4] DEFAULT ('') NOT NULL,
|
|
[Campo11_1_5] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo11_1_5] DEFAULT ('') NOT NULL,
|
|
[Campo11_2_1] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo11_2_1] DEFAULT ('') NOT NULL,
|
|
[Campo11_2_2] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo11_2_2] DEFAULT ('') NOT NULL,
|
|
[Campo11_2_3] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo11_2_3] DEFAULT ('') NOT NULL,
|
|
[Campo11_2_4] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo11_2_4] DEFAULT ('') NOT NULL,
|
|
[Campo11_2_5] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo11_2_5] DEFAULT ('') NOT NULL,
|
|
[Campo12_1] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo12_1] DEFAULT ('') NOT NULL,
|
|
[Campo12_2] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo12_2] DEFAULT ('') NOT NULL,
|
|
[Campo12_3] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo12_3] DEFAULT ('') NOT NULL,
|
|
[Campo12_4] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo12_4] DEFAULT ('') NOT NULL,
|
|
[Campo12_5] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo12_5] DEFAULT ('') NOT NULL,
|
|
[Campo13_1] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo13_1] DEFAULT ('') NOT NULL,
|
|
[Campo13_2] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo13_2] DEFAULT ('') NOT NULL,
|
|
[Campo13_3] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo13_3] DEFAULT ('') NOT NULL,
|
|
[Campo13_4] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo13_4] DEFAULT ('') NOT NULL,
|
|
[Campo13_5] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo13_5] DEFAULT ('') NOT NULL,
|
|
[Campo14_1] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo14_1] DEFAULT ('') NOT NULL,
|
|
[Campo14_2] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo14_2] DEFAULT ('') NOT NULL,
|
|
[Campo14_3] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo14_3] DEFAULT ('') NOT NULL,
|
|
[Campo14_4] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo14_4] DEFAULT ('') NOT NULL,
|
|
[Campo14_5] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo14_5] DEFAULT ('') NOT NULL,
|
|
[Campo15_1] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo15_1] DEFAULT ('') NOT NULL,
|
|
[Campo15_2] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo15_2] DEFAULT ('') NOT NULL,
|
|
[Campo15_3] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo15_3] DEFAULT ('') NOT NULL,
|
|
[Campo15_4] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo15_4] DEFAULT ('') NOT NULL,
|
|
[Campo15_5] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo15_5] DEFAULT ('') NOT NULL,
|
|
[Campo16_1] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo16_1] DEFAULT ('') NOT NULL,
|
|
[Campo16_2] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo16_2] DEFAULT ('') NOT NULL,
|
|
[Campo16_3] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo16_3] DEFAULT ('') NOT NULL,
|
|
[Campo16_4] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo16_4] DEFAULT ('') NOT NULL,
|
|
[Campo16_5] NVARCHAR (50) CONSTRAINT [DF_Odette_Campo16_5] DEFAULT ('') NOT NULL,
|
|
[StatoOk] BIT CONSTRAINT [DF_Odette_StatoRecordOdette] DEFAULT ((0)) NOT NULL,
|
|
[CodCS] NCHAR (2) CONSTRAINT [DF_Odette_CodCS] DEFAULT ('') NOT NULL,
|
|
[CodMag] NVARCHAR (2) CONSTRAINT [DF_Odette_CodMag] DEFAULT ('') NOT NULL,
|
|
[GrpBolla] NVARCHAR (1) CONSTRAINT [DF_Odette_GrpBolla] DEFAULT ('') NOT NULL,
|
|
[DataBolla] NUMERIC (8) CONSTRAINT [DF_Odette_DataBolla] DEFAULT ((0)) NOT NULL,
|
|
[NumBolla] NUMERIC (6) CONSTRAINT [DF_Odette_NumBolla] DEFAULT ((0)) NOT NULL,
|
|
[Particolare] NVARCHAR (15) CONSTRAINT [DF_Odette_Particolare] DEFAULT ('') NOT NULL,
|
|
[CodLista] NVARCHAR (12) CONSTRAINT [DF_Odette_CodLista] DEFAULT ('') NOT NULL,
|
|
CONSTRAINT [PK_Odette] PRIMARY KEY CLUSTERED ([CampoUDC] ASC) WITH (FILLFACTOR = 95)
|
|
);
|
|
|
|
|
|
GO
|
|
|
|
|
|
-- =============================================
|
|
-- Author: Samuele E. Locatelli
|
|
-- Create date: 2011-05-20
|
|
-- Description: trigger x inserimento in tab
|
|
-- storico record cancellati
|
|
-- =============================================
|
|
CREATE TRIGGER [dbo].[trg_odetteDel]
|
|
ON [dbo].[Odette]
|
|
AFTER DELETE
|
|
AS
|
|
BEGIN
|
|
|
|
-- setup variabili e verbosità
|
|
SET NOCOUNT ON;
|
|
-- inserisco in storico
|
|
INSERT INTO Odette_storico
|
|
(DataOraDelete,CampoUDC,Campo1_1,Campo1_2,Campo1_3,Campo1_4,Campo1_5,Campo2_1,Campo2_2,Campo2_3,Campo2_4,Campo2_5,Campo3_1,
|
|
Campo3_2,Campo3_3,Campo3_4,Campo3_5,Campo4_1,Campo4_2,Campo4_3,Campo4_4,Campo4_5,Campo5_1,Campo5_2,Campo5_3,Campo5_4,Campo5_5,
|
|
Campo6_1,Campo6_2,Campo6_3,Campo6_4,Campo6_5,Campo7_1,Campo7_2,Campo7_3,Campo7_4,Campo7_5,Campo8_1,Campo8_2,Campo8_3,Campo8_4,
|
|
Campo8_5,Campo9_1,Campo9_2,Campo9_3,Campo9_4,Campo9_5,Campo10_1,Campo10_2,Campo10_3,Campo10_4,Campo10_5,Campo11_1_1,Campo11_1_2,
|
|
Campo11_1_3,Campo11_1_4,Campo11_1_5,Campo11_2_1,Campo11_2_2,Campo11_2_3,Campo11_2_4,Campo11_2_5,Campo12_1,Campo12_2,Campo12_3,
|
|
Campo12_4,Campo12_5,Campo13_1,Campo13_2,Campo13_3,Campo13_4,Campo13_5,Campo14_1,Campo14_2,Campo14_3,Campo14_4,Campo14_5,Campo15_1,
|
|
Campo15_2,Campo15_3,Campo15_4,Campo15_5,Campo16_1,Campo16_2,Campo16_3,Campo16_4,Campo16_5,StatoOk,CodCS,CodMag,GrpBolla,DataBolla,
|
|
NumBolla,Particolare,CodLista)
|
|
|
|
SELECT GETDATE(), CampoUDC,Campo1_1,Campo1_2,Campo1_3,Campo1_4,Campo1_5,Campo2_1,Campo2_2,Campo2_3,Campo2_4,Campo2_5,Campo3_1,
|
|
Campo3_2,Campo3_3,Campo3_4,Campo3_5,Campo4_1,Campo4_2,Campo4_3,Campo4_4,Campo4_5,Campo5_1,Campo5_2,Campo5_3,Campo5_4,Campo5_5,
|
|
Campo6_1,Campo6_2,Campo6_3,Campo6_4,Campo6_5,Campo7_1,Campo7_2,Campo7_3,Campo7_4,Campo7_5,Campo8_1,Campo8_2,Campo8_3,Campo8_4,
|
|
Campo8_5,Campo9_1,Campo9_2,Campo9_3,Campo9_4,Campo9_5,Campo10_1,Campo10_2,Campo10_3,Campo10_4,Campo10_5,Campo11_1_1,Campo11_1_2,
|
|
Campo11_1_3,Campo11_1_4,Campo11_1_5,Campo11_2_1,Campo11_2_2,Campo11_2_3,Campo11_2_4,Campo11_2_5,Campo12_1,Campo12_2,Campo12_3,
|
|
Campo12_4,Campo12_5,Campo13_1,Campo13_2,Campo13_3,Campo13_4,Campo13_5,Campo14_1,Campo14_2,Campo14_3,Campo14_4,Campo14_5,Campo15_1,
|
|
Campo15_2,Campo15_3,Campo15_4,Campo15_5,Campo16_1,Campo16_2,Campo16_3,Campo16_4,Campo16_5,StatoOk,CodCS,CodMag,GrpBolla,DataBolla,
|
|
NumBolla,Particolare,CodLista
|
|
FROM deleted
|
|
|
|
END
|
|
|