[CIMPRODUCT - Triggers OF] Déclencheurs sur OF #cimproduct #sql #trigger

CIMPRODUCT - TG_OF_Insert.sql

USE [Cimprod_Mithieux]
GO

/****** Object:  Trigger [dbo].[TG_OF_Insert]    Script Date: 09/02/2022 11:59:39 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[TG_OF_Insert] ON [dbo].[OF]
AFTER INSERT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    UPDATE [OF] SET DateCreation = getdate(), DateModification = GETDATE() FROM inserted WHERE [OF].IdOF = inserted.IdOF

	IF UPDATE(Cloture)
	BEGIN
		DECLARE @Cloture bit;
		DECLARE @IdMetaOF integer;
		DECLARE @DateCloture datetime;

		SELECT @IdMetaOF = Inserted.IdMetaOF FROM Inserted;
		IF @IdMetaOF > 0
		BEGIN
			SET @Cloture = dbo.F_VerifClotureMetaOF(@IdMetaOF);

			IF @Cloture = 1
				SELECT @DateCloture = MAX([OF].DateCloture) FROM [OF] WHERE [OF].IdMetaOF = @IdMetaOF

			UPDATE [OF] SET Cloture = @Cloture, DateCloture = (CASE WHEN @Cloture = 0 THEN NULL ELSE @DateCloture END) 
			FROM Inserted
			WHERE [OF].IdOF = @IdMetaOF;
		END
	END
END
GO

ALTER TABLE [dbo].[OF] ENABLE TRIGGER [TG_OF_Insert]
GO

CIMPRODUCT - TG_OF_Update.sql

USE [Cimprod_Mithieux]
GO

/****** Object:  Trigger [dbo].[TG_OF_Update]    Script Date: 09/02/2022 11:58:21 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[TG_OF_Update] ON [dbo].[OF]
AFTER UPDATE
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    IF NOT UPDATE(DateModification)
		UPDATE [OF] SET DateModification = getdate() FROM inserted WHERE [OF].IdOF = inserted.IdOF

	IF UPDATE(Cloture)
	BEGIN
		DECLARE @Cloture bit;
		DECLARE @IdMetaOF integer;
		DECLARE @DateCloture datetime;

		SELECT @IdMetaOF = Inserted.IdMetaOF FROM Inserted;
		IF @IdMetaOF > 0
		BEGIN
			SET @Cloture = dbo.F_VerifClotureMetaOF(@IdMetaOF);

			IF @Cloture = 1
				SELECT @DateCloture = MAX([OF].DateCloture) FROM [OF] WHERE [OF].IdMetaOF = @IdMetaOF

			UPDATE [OF] SET Cloture = @Cloture, DateCloture = (CASE WHEN @Cloture = 0 THEN NULL ELSE @DateCloture END) 
			FROM Inserted
			WHERE [OF].IdOF = @IdMetaOF;
		END
	END

END
GO

ALTER TABLE [dbo].[OF] ENABLE TRIGGER [TG_OF_Update]
GO