CODEWITHSUNDEEP
sqlsql-server-2008
user2179026
user2179026

Reputation:

How update a field on the basis of condition

I have written store procedures as below,

INSERT INTO Slider
  (
    SliderID
   ,SliderTitle
   ,SliderDescription
   ,SliderImage
   ,BackgroundImage
   ,IsBackgroundEnabled
   ,PageName
   ,IsEnabled
   ,CreatedOn
  )
VALUES
  (
    NEWID()
   ,@SliderTitle
   ,@SliderDescription
   ,@SliderImage
   ,@BackgroundImage
   ,@IsBackgroundEnabled
   ,@PageName
   ,@IsEnabled
   ,GETDATE()

Now I want that if there are more than one record in slider table and currently adding record's @IsBackgroundEnabled property is true or 1 then all the other record's @IsBackgroundEnabled property should become false or 0.

Note: That currently added record's @IsBackgroundEnabled should be true.

Please help me!!

Upvotes: 0

Views: 51

Answers (2)

Pedram
Pedram

Reputation: 6508

For that, you may have to create trigger on Slider table like below,

CREATE TRIGGER SliderAfterInsert
ON [dbo].[Slider]
FOR  INSERT
AS
    DECLARE @latest AS INT 
    SET @latest = (
            SELECT TOP 1 IsBackgroundEnabled
            FROM   [Slider]
            WHERE  IsBackgroundEnabled = 1
            ORDER BY
                   CreatedOn
        )

    IF (@latest IS NOT NULL AND @latest<>0)
    BEGIN
        UPDATE Slider
        SET    IsBackgroundEnabled     = 0
        WHERE  CreatedOn               = GETDATE()
        PRINT 'SliderAfterInsert trigger fired.'
    END
GO

This will definitely help you.

Upvotes: 0

Adish
Adish

Reputation: 691

Before your insert statement add this update statement:

IF @IsBackgroundEnabled = 1
UPDATE Slider 
SET IsBackgroundEnabled=0

Then add your insert statement

Upvotes: 1

Related Questions