SaidSoighiri94
101 lines
4.2 KiB
Transact-SQL
101 lines
4.2 KiB
Transact-SQL
BEGIN TRY
|
|
|
|
BEGIN TRAN;
|
|
|
|
-- CreateTable
|
|
CREATE TABLE [dbo].[anomalie] (
|
|
[id] INT NOT NULL IDENTITY(1,1),
|
|
[emprunt_id] INT NOT NULL,
|
|
[materiel_id] INT NOT NULL,
|
|
[utilisateur_id] INT NOT NULL,
|
|
[traitee_par_id] INT,
|
|
[type] NVARCHAR(1000) NOT NULL,
|
|
[description] NVARCHAR(1000) NOT NULL,
|
|
[statut] NVARCHAR(1000) NOT NULL,
|
|
[detectee_automatiquement] BIT NOT NULL CONSTRAINT [anomalie_detectee_automatiquement_df] DEFAULT 1,
|
|
[observation] NVARCHAR(1000),
|
|
[date_detection] DATETIME2 NOT NULL,
|
|
[date_resolution] DATETIME2,
|
|
[created_at] DATETIME2 NOT NULL CONSTRAINT [anomalie_created_at_df] DEFAULT CURRENT_TIMESTAMP,
|
|
[updated_at] DATETIME2 NOT NULL,
|
|
CONSTRAINT [anomalie_pkey] PRIMARY KEY CLUSTERED ([id])
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE [dbo].[notification] (
|
|
[id] INT NOT NULL IDENTITY(1,1),
|
|
[utilisateur_id] INT NOT NULL,
|
|
[anomalie_id] INT,
|
|
[titre] NVARCHAR(1000) NOT NULL,
|
|
[message] NVARCHAR(1000) NOT NULL,
|
|
[type] NVARCHAR(1000) NOT NULL,
|
|
[lu] BIT NOT NULL CONSTRAINT [notification_lu_df] DEFAULT 0,
|
|
[date_creation] DATETIME2 NOT NULL CONSTRAINT [notification_date_creation_df] DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT [notification_pkey] PRIMARY KEY CLUSTERED ([id])
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE [dbo].[historique] (
|
|
[id] INT NOT NULL IDENTITY(1,1),
|
|
[utilisateur_id] INT NOT NULL,
|
|
[emprunt_id] INT,
|
|
[materiel_id] INT,
|
|
[campus_id] INT,
|
|
[salle_pret_id] INT,
|
|
[poste_emprunt_id] INT,
|
|
[action] NVARCHAR(1000) NOT NULL,
|
|
[description] NVARCHAR(1000) NOT NULL,
|
|
[date_action] DATETIME2 NOT NULL,
|
|
[created_at] DATETIME2 NOT NULL CONSTRAINT [historique_created_at_df] DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT [historique_pkey] PRIMARY KEY CLUSTERED ([id])
|
|
);
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE [dbo].[anomalie] ADD CONSTRAINT [anomalie_emprunt_id_fkey] FOREIGN KEY ([emprunt_id]) REFERENCES [dbo].[emprunt]([id]) ON DELETE NO ACTION ON UPDATE NO ACTION;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE [dbo].[anomalie] ADD CONSTRAINT [anomalie_materiel_id_fkey] FOREIGN KEY ([materiel_id]) REFERENCES [dbo].[materiel]([id]) ON DELETE NO ACTION ON UPDATE NO ACTION;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE [dbo].[anomalie] ADD CONSTRAINT [anomalie_utilisateur_id_fkey] FOREIGN KEY ([utilisateur_id]) REFERENCES [dbo].[utilisateur]([id]) ON DELETE NO ACTION ON UPDATE NO ACTION;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE [dbo].[anomalie] ADD CONSTRAINT [anomalie_traitee_par_id_fkey] FOREIGN KEY ([traitee_par_id]) REFERENCES [dbo].[utilisateur]([id]) ON DELETE NO ACTION ON UPDATE NO ACTION;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE [dbo].[notification] ADD CONSTRAINT [notification_utilisateur_id_fkey] FOREIGN KEY ([utilisateur_id]) REFERENCES [dbo].[utilisateur]([id]) ON DELETE NO ACTION ON UPDATE NO ACTION;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE [dbo].[notification] ADD CONSTRAINT [notification_anomalie_id_fkey] FOREIGN KEY ([anomalie_id]) REFERENCES [dbo].[anomalie]([id]) ON DELETE NO ACTION ON UPDATE NO ACTION;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE [dbo].[historique] ADD CONSTRAINT [historique_utilisateur_id_fkey] FOREIGN KEY ([utilisateur_id]) REFERENCES [dbo].[utilisateur]([id]) ON DELETE NO ACTION ON UPDATE NO ACTION;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE [dbo].[historique] ADD CONSTRAINT [historique_emprunt_id_fkey] FOREIGN KEY ([emprunt_id]) REFERENCES [dbo].[emprunt]([id]) ON DELETE NO ACTION ON UPDATE NO ACTION;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE [dbo].[historique] ADD CONSTRAINT [historique_materiel_id_fkey] FOREIGN KEY ([materiel_id]) REFERENCES [dbo].[materiel]([id]) ON DELETE NO ACTION ON UPDATE NO ACTION;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE [dbo].[historique] ADD CONSTRAINT [historique_campus_id_fkey] FOREIGN KEY ([campus_id]) REFERENCES [dbo].[campus]([id]) ON DELETE NO ACTION ON UPDATE NO ACTION;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE [dbo].[historique] ADD CONSTRAINT [historique_salle_pret_id_fkey] FOREIGN KEY ([salle_pret_id]) REFERENCES [dbo].[salle_pret]([id]) ON DELETE NO ACTION ON UPDATE NO ACTION;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE [dbo].[historique] ADD CONSTRAINT [historique_poste_emprunt_id_fkey] FOREIGN KEY ([poste_emprunt_id]) REFERENCES [dbo].[poste_emprunt]([id]) ON DELETE NO ACTION ON UPDATE NO ACTION;
|
|
|
|
COMMIT TRAN;
|
|
|
|
END TRY
|
|
BEGIN CATCH
|
|
|
|
IF @@TRANCOUNT > 0
|
|
BEGIN
|
|
ROLLBACK TRAN;
|
|
END;
|
|
THROW
|
|
|
|
END CATCH
|