Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2005
    Posts
    30

    Unanswered: Trigger to update current row

    I am trying to create a trigger to update a field in the row I have just inserted but it isn't working:
    Code:
    ALTER TRIGGER [dbo].[trig_Update_TBL_PC]
    ON [dbo].[TBL_PC]
    AFTER INSERT
    AS
    DECLARE @rowid int 
    SELECT @rowid = SCOPE_IDENTITY();
    
    
      BEGIN
      UPDATE [TBL_PC SET ACTIVE= 'ON' WHERE @@IDENTITY=@rowid
    
      END
    It doesn't give an error; just wondering where I am going wrong?

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Code:
    CREATE TABLE TBL_PC(
    	Id INT	NOT NULL,
    	Active	CHAR(3) NOT NULL DEfault 'Off'
    		Constraint cc_Tbl_PC_Active CHECK (Active IN ('On', 'Off'))
    )
    
    INSERT INTO TBL_PC(Id) VALUES
    (1), (2), (3), (4)
    
    SELECT * FROM TBL_PC
    
    CREATE TRIGGER [dbo].[trig_Update_TBL_PC]
    ON [dbo].[TBL_PC]
    AFTER INSERT
    AS
    BEGIN
    	SET NOCOUNT ON;
    	
    	UPDATE U
    	SET Active = 'ON'
    	FROM [TBL_PC] as  U
    		INNER JOIN inserted ON
    		U.Id = inserted.Id;
    END
     
    INSERT INTO TBL_PC(Id) VALUES
    (5)
    
    INSERT INTO TBL_PC(Id) 
    SELECT Id + 10
    FROM TBL_PC
    WHERE Id < 10
    
    SELECT * FROM TBL_PC
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Feb 2005
    Posts
    30
    That worked thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •