-- SQL Manager 2010 for SQL Server 3.6.0.1
-- ---------------------------------------
-- Host      : CLA-SERVER-1000
-- Database  : test
-- Version   : Microsoft SQL Server  10.50.1600.1


CREATE DATABASE [test]
ON PRIMARY
  ( NAME = [test],
    FILENAME = N'D:\SQLServer\2008EE_CRACKED\MSSQL10_50.MSSQLSERVER_EE_CRACKED\MSSQL\DATA\test.mdf',
    SIZE = 2304 KB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 1 MB )
LOG ON
  ( NAME = [test_log],
    FILENAME = N'D:\SQLServer\2008EE_CRACKED\MSSQL10_50.MSSQLSERVER_EE_CRACKED\MSSQL\DATA\test_log.LDF',
    SIZE = 576 KB,
    MAXSIZE = 2097152 MB,
    FILEGROWTH = 10 % )
COLLATE French_CI_AS
GO

USE [test]
GO

--
-- Definition for table Status : 
--

CREATE TABLE [dbo].[Status] (
  [Id] int IDENTITY(1, 1) NOT NULL,
  [Code] int NOT NULL
)
ON [PRIMARY]
GO

--
-- Definition for table OtherTable : 
--

CREATE TABLE [dbo].[OtherTable] (
  [Id] int IDENTITY(1, 1) NOT NULL,
  [Number] int NOT NULL
)
ON [PRIMARY]
GO

--
-- Definition for table C_Data : 
--

CREATE TABLE [dbo].[C_Data] (
  [Id] int IDENTITY(1, 1) NOT NULL,
  [C_Id] int NOT NULL,
  [DataKey] int NOT NULL,
  [DataValue] varchar(255) COLLATE French_CI_AS NOT NULL
)
ON [PRIMARY]
GO

--
-- Definition for table ArchiveC_Data : 
--

CREATE TABLE [dbo].[ArchiveC_Data] (
  [Id] int IDENTITY(1, 1) NOT NULL,
  [ArchiveC_Id] int NOT NULL,
  [DataKey] int NOT NULL,
  [DataValue] varchar(255) COLLATE French_CI_AS NOT NULL
)
ON [PRIMARY]
GO

--
-- Definition for table ArchiveC : 
--

CREATE TABLE [dbo].[ArchiveC] (
  [Id] int IDENTITY(1, 1) NOT NULL,
  [C_Id] int NOT NULL,
  [BName] varchar(50) COLLATE French_CI_AS NOT NULL,
  [OtherTableNumber] int NOT NULL
)
ON [PRIMARY]
GO

EXEC sp_addextendedproperty 'MS_Description', N'Clé temporaire pour la liaison des DATA', 'schema', 'dbo', 'table', 'ArchiveC', 'column', 'C_Id'
GO

--
-- Definition for table C : 
--

CREATE TABLE [dbo].[C] (
  [Id] int IDENTITY(1, 1) NOT NULL,
  [OtherTable_Id] int NOT NULL,
  [B_Id] int NOT NULL
)
ON [PRIMARY]
GO

--
-- Definition for table B : 
--

CREATE TABLE [dbo].[B] (
  [Id] int IDENTITY(1, 1) NOT NULL,
  [A_Id] int NOT NULL,
  [Name] varchar(50) COLLATE French_CI_AS NOT NULL,
  [Status_Id] int NOT NULL
)
ON [PRIMARY]
GO

--
-- Definition for table A : 
--

CREATE TABLE [dbo].[A] (
  [Id] int IDENTITY(1, 1) NOT NULL,
  [Name] varchar(255) COLLATE French_CI_AS NOT NULL
)
ON [PRIMARY]
GO

--
-- Data for table dbo.A  (LIMIT 0,500)
--

SET IDENTITY_INSERT [dbo].[A] ON
GO

INSERT INTO [dbo].[A] ([Id], [Name])
VALUES 
  (1, N'toto')
GO

INSERT INTO [dbo].[A] ([Id], [Name])
VALUES 
  (2, N'titi')
GO

INSERT INTO [dbo].[A] ([Id], [Name])
VALUES 
  (3, N'tata')
GO

SET IDENTITY_INSERT [dbo].[A] OFF
GO

--
-- Data for table dbo.B  (LIMIT 0,500)
--

SET IDENTITY_INSERT [dbo].[B] ON
GO

INSERT INTO [dbo].[B] ([Id], [A_Id], [Name], [Status_Id])
VALUES 
  (1, 1, N'1', 1)
GO

INSERT INTO [dbo].[B] ([Id], [A_Id], [Name], [Status_Id])
VALUES 
  (2, 1, N'2', 1)
GO

INSERT INTO [dbo].[B] ([Id], [A_Id], [Name], [Status_Id])
VALUES 
  (3, 1, N'3', 1)
GO

INSERT INTO [dbo].[B] ([Id], [A_Id], [Name], [Status_Id])
VALUES 
  (4, 2, N'10', 1)
GO

INSERT INTO [dbo].[B] ([Id], [A_Id], [Name], [Status_Id])
VALUES 
  (5, 2, N'11', 1)
GO

INSERT INTO [dbo].[B] ([Id], [A_Id], [Name], [Status_Id])
VALUES 
  (6, 2, N'12', 1)
GO

INSERT INTO [dbo].[B] ([Id], [A_Id], [Name], [Status_Id])
VALUES 
  (7, 3, N'20', 1)
GO

SET IDENTITY_INSERT [dbo].[B] OFF
GO

--
-- Data for table dbo.C  (LIMIT 0,500)
--

SET IDENTITY_INSERT [dbo].[C] ON
GO

INSERT INTO [dbo].[C] ([Id], [OtherTable_Id], [B_Id])
VALUES 
  (1, 1, 1)
GO

INSERT INTO [dbo].[C] ([Id], [OtherTable_Id], [B_Id])
VALUES 
  (2, 1, 2)
GO

INSERT INTO [dbo].[C] ([Id], [OtherTable_Id], [B_Id])
VALUES 
  (3, 1, 2)
GO

SET IDENTITY_INSERT [dbo].[C] OFF
GO

--
-- Data for table dbo.C_Data  (LIMIT 0,500)
--

SET IDENTITY_INSERT [dbo].[C_Data] ON
GO

INSERT INTO [dbo].[C_Data] ([Id], [C_Id], [DataKey], [DataValue])
VALUES 
  (1, 1, 1, N'valeur1')
GO

INSERT INTO [dbo].[C_Data] ([Id], [C_Id], [DataKey], [DataValue])
VALUES 
  (2, 1, 2, N'valeur2')
GO

INSERT INTO [dbo].[C_Data] ([Id], [C_Id], [DataKey], [DataValue])
VALUES 
  (3, 1, 3, N'valeur3')
GO

INSERT INTO [dbo].[C_Data] ([Id], [C_Id], [DataKey], [DataValue])
VALUES 
  (4, 1, 4, N'valeur4')
GO

INSERT INTO [dbo].[C_Data] ([Id], [C_Id], [DataKey], [DataValue])
VALUES 
  (5, 2, 1, N'toto1')
GO

INSERT INTO [dbo].[C_Data] ([Id], [C_Id], [DataKey], [DataValue])
VALUES 
  (6, 2, 2, N'toto2')
GO

SET IDENTITY_INSERT [dbo].[C_Data] OFF
GO

--
-- Data for table dbo.OtherTable  (LIMIT 0,500)
--

SET IDENTITY_INSERT [dbo].[OtherTable] ON
GO

INSERT INTO [dbo].[OtherTable] ([Id], [Number])
VALUES 
  (1, 1234)
GO

SET IDENTITY_INSERT [dbo].[OtherTable] OFF
GO

--
-- Data for table dbo.Status  (LIMIT 0,500)
--

SET IDENTITY_INSERT [dbo].[Status] ON
GO

INSERT INTO [dbo].[Status] ([Id], [Code])
VALUES 
  (1, 1)
GO

INSERT INTO [dbo].[Status] ([Id], [Code])
VALUES 
  (2, 5)
GO

SET IDENTITY_INSERT [dbo].[Status] OFF
GO

--
-- Definition for indices : 
--

ALTER TABLE [dbo].[Status]
ADD PRIMARY KEY CLUSTERED ([Id])
WITH (
  PAD_INDEX = OFF,
  IGNORE_DUP_KEY = OFF,
  STATISTICS_NORECOMPUTE = OFF,
  ALLOW_ROW_LOCKS = ON,
  ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO

ALTER TABLE [dbo].[OtherTable]
ADD PRIMARY KEY CLUSTERED ([Id])
WITH (
  PAD_INDEX = OFF,
  IGNORE_DUP_KEY = OFF,
  STATISTICS_NORECOMPUTE = OFF,
  ALLOW_ROW_LOCKS = ON,
  ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO

ALTER TABLE [dbo].[C_Data]
ADD PRIMARY KEY CLUSTERED ([Id])
WITH (
  PAD_INDEX = OFF,
  IGNORE_DUP_KEY = OFF,
  STATISTICS_NORECOMPUTE = OFF,
  ALLOW_ROW_LOCKS = ON,
  ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO

ALTER TABLE [dbo].[ArchiveC_Data]
ADD PRIMARY KEY CLUSTERED ([Id])
WITH (
  PAD_INDEX = OFF,
  IGNORE_DUP_KEY = OFF,
  STATISTICS_NORECOMPUTE = OFF,
  ALLOW_ROW_LOCKS = ON,
  ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO

ALTER TABLE [dbo].[ArchiveC]
ADD PRIMARY KEY CLUSTERED ([Id])
WITH (
  PAD_INDEX = OFF,
  IGNORE_DUP_KEY = OFF,
  STATISTICS_NORECOMPUTE = OFF,
  ALLOW_ROW_LOCKS = ON,
  ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO

ALTER TABLE [dbo].[C]
ADD PRIMARY KEY CLUSTERED ([Id])
WITH (
  PAD_INDEX = OFF,
  IGNORE_DUP_KEY = OFF,
  STATISTICS_NORECOMPUTE = OFF,
  ALLOW_ROW_LOCKS = ON,
  ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO

ALTER TABLE [dbo].[B]
ADD PRIMARY KEY CLUSTERED ([Id])
WITH (
  PAD_INDEX = OFF,
  IGNORE_DUP_KEY = OFF,
  STATISTICS_NORECOMPUTE = OFF,
  ALLOW_ROW_LOCKS = ON,
  ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO

ALTER TABLE [dbo].[A]
ADD PRIMARY KEY CLUSTERED ([Id])
WITH (
  PAD_INDEX = OFF,
  IGNORE_DUP_KEY = OFF,
  STATISTICS_NORECOMPUTE = OFF,
  ALLOW_ROW_LOCKS = ON,
  ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO

--
-- Definition for foreign keys : 
--

ALTER TABLE [dbo].[C_Data]
ADD FOREIGN KEY ([C_Id]) 
  REFERENCES [dbo].[C] ([Id]) 
  ON UPDATE NO ACTION
  ON DELETE NO ACTION
GO

ALTER TABLE [dbo].[ArchiveC_Data]
ADD FOREIGN KEY ([ArchiveC_Id]) 
  REFERENCES [dbo].[ArchiveC] ([Id]) 
  ON UPDATE NO ACTION
  ON DELETE NO ACTION
GO

ALTER TABLE [dbo].[C]
ADD FOREIGN KEY ([B_Id]) 
  REFERENCES [dbo].[B] ([Id]) 
  ON UPDATE NO ACTION
  ON DELETE NO ACTION
GO

ALTER TABLE [dbo].[C]
ADD FOREIGN KEY ([OtherTable_Id]) 
  REFERENCES [dbo].[OtherTable] ([Id]) 
  ON UPDATE NO ACTION
  ON DELETE NO ACTION
GO

ALTER TABLE [dbo].[B]
ADD FOREIGN KEY ([A_Id]) 
  REFERENCES [dbo].[A] ([Id]) 
  ON UPDATE NO ACTION
  ON DELETE NO ACTION
GO

ALTER TABLE [dbo].[B]
ADD CONSTRAINT [FK_B_Status] FOREIGN KEY ([Status_Id]) 
  REFERENCES [dbo].[Status] ([Id]) 
  ON UPDATE NO ACTION
  ON DELETE NO ACTION
GO

--
-- Definition for triggers : 
--
GO
CREATE TRIGGER [dbo].[ArchiveC_tri] ON [dbo].[ArchiveC]
WITH EXECUTE AS CALLER
FOR INSERT
AS
BEGIN
  INSERT INTO ArchiveC_Data (ArchiveC_Id, DataKey, DataValue)
    SELECT INSERTED.Id, DataKey, DataValue
    FROM INSERTED INNER JOIN C_Data ON INSERTED.C_Id=C_Data.C_Id
END
GO

CREATE TRIGGER [dbo].[C_trd] ON [dbo].[C]
WITH EXECUTE AS CALLER
INSTEAD OF DELETE
AS
BEGIN
  INSERT INTO ArchiveC(C_Id, BName, OtherTableNumber)
    SELECT DELETED.Id, B.Name, OtherTable.Number
    FROM DELETED INNER JOIN B ON B.Id=B_Id
                 INNER JOIN OtherTable ON OtherTable.Id=OtherTable_Id

  DELETE FROM C_Data WHERE C_Id IN (SELECT Id FROM DELETED)
  DELETE FROM C WHERE Id IN (SELECT Id FROM DELETED)
END
GO

CREATE TRIGGER [dbo].[B_trd] ON [dbo].[B]
WITH EXECUTE AS CALLER
INSTEAD OF DELETE
AS
BEGIN
  DELETE FROM C WHERE B_Id IN (SELECT Id FROM DELETED)
  DELETE FROM B WHERE Id IN (SELECT Id FROM DELETED)
END
GO

CREATE TRIGGER [dbo].[B_tru] ON [dbo].[B]
WITH EXECUTE AS CALLER
FOR UPDATE
AS
BEGIN
  DELETE FROM B WHERE Id IN (SELECT INSERTED.Id FROM INSERTED INNER JOIN Status ON Status.Id=Status_Id WHERE Status.Code=5)
END
GO

CREATE TRIGGER [dbo].[A_trd] ON [dbo].[A]
WITH EXECUTE AS CALLER
INSTEAD OF DELETE
AS
BEGIN
  DELETE FROM B WHERE A_Id IN (SELECT Id FROM DELETED)
  DELETE FROM A WHERE Id IN (SELECT Id FROM DELETED)
END
GO