CODEWITHSUNDEEP
sql-server-2005triggers
sohail khan
sohail khan

Reputation: 1

How to use trigger for update in SQL Server 2005

I have a table called tbl_gallery which has a column of datatype bit called isActive.

When the user updates the IsActive value, other rows with IsActive = true will be automatically turned to false.

How can do it with updated trigger?

Please help

Upvotes: 0

Views: 661

Answers (2)

MohammadReza dehnashi
MohammadReza dehnashi

Reputation: 1

Trigger for update second table after updated first table :

CREATE TRIGGER update_table_cityUpdated_afterTable_cityUpdate
  ON Table_city 
  AFTER UPDATE AS
BEGIN
  DECLARE @cityId AS BIGINT
  DECLARE @stateId AS BIGINT
  DECLARE @CityName AS NVARCHAR(200)

  SELECT @cityId=cityId FROM INSERTED
  SELECT @stateId= stateId FROM INSERTED
  SELECT @CityName= CityName FROM INSERTED
  UPDATE table_cityUpdated
 SET
    [dbo].[table_cityUpdated].stateId=@stateId,
    [dbo].[table_cityUpdated].CityName=@CityName
  WHERE [dbo].[table_cityUpdated].cityId=@cityId
END
;

Upvotes: 0

StuartLC
StuartLC

Reputation: 107247

I think you want something like:

CREATE TRIGGER trgGalleryActive
ON dbo.tbl_gallery
FOR UPDATE
AS
    BEGIN
        UPDATE g
        -- Update all other gallery rows for this same user to false
            SET g.IsActive = 0
            FROM tbl_gallery g
                INNER JOIN inserted i
                on g.UserPK = i.UserPK
            WHERE
                -- However, we don't want current inserted records to be updated
                g.TablePK <> i.TablePK
                            -- As per Marc's comment - don't update existing inactive rows unnecessarily
                            AND g.IsActive = 1
                            -- Only if this record is active should any of this happen
                            AND i.IsActive = 1
    END

Upvotes: 1

Related Questions