locked
Update a column value in Inserted table? RRS feed

  • Question

  • Can I update the column value in an Inserted table on an After Insert trigger? I think the answer is no. But kind of curious to find the answer.

    Thanks
    Thursday, February 5, 2009 2:35 PM

Answers

  •  do you mean something like this?
    CREATE TABLE dbo.employees (  
       employee_id int identity(1,1)  
     , forename    varchar(25)  
     , surname     varchar(25)  
     , ssn         varchar(10)  
    )  
    GO  
     
    CREATE TRIGGER dbo.employees_instead_of_update  
      ON dbo.employees  
      INSTEAD OF INSERT 
    AS 
      BEGIN 
        INSERT INTO dbo.employees (forename, surname, ssn)  
        SELECT forename  
             , surname  
             , NULL 
        FROM   inserted  
     
        /*--Alternative method  
        INSERT INTO dbo.employees (forename, surname)  
        SELECT forename  
             , surname  
        FROM   inserted  
        */  
      END 
    GO  
     
    --Single insert  
    INSERT INTO dbo.employees (forename, surname, ssn)  
      VALUES ('A''A''A')  
     
    --Multiple inserts  
    INSERT INTO dbo.employees (forename, surname, ssn)  
          SELECT 'B''B''B' 
    UNION SELECT 'C''C''C' 
     
     
    SELECT *  
    FROM   dbo.employees  
     
    GO  
    DROP TABLE dbo.employees 

    George
    • Marked as answer by Yum64147 Thursday, February 5, 2009 3:44 PM
    Thursday, February 5, 2009 3:36 PM
    Answerer

All replies

  • the virtual tables inserted and deleted are read-only.
    I can't for the life of me think of a reason why you'd want to update them either!


    George
    Thursday, February 5, 2009 2:39 PM
    Answerer
  • In case you wanted more than just my word for it:

    From Books OnlineUsing the inserted and deleted Tables

    "You cannot directly modify the data in the tables or perform data definition language (DDL) operations on the tables, such as CREATE INDEX."


    George
    Thursday, February 5, 2009 2:44 PM
    Answerer
  • Sorry. What I have now is a before insert trigger. It is like updating/inserting a social security number by an user. So I have a flag on that column that it is not updatable. So before an insert occur I am trying to update the value of the INSERTED column to null then the REAL insert happen with null. Make sense? 
    I am converting that old trigger to SS using an INSTEAD OF TRIGGER.

    Thanks
    Thursday, February 5, 2009 2:49 PM
  •  do you mean something like this?
    CREATE TABLE dbo.employees (  
       employee_id int identity(1,1)  
     , forename    varchar(25)  
     , surname     varchar(25)  
     , ssn         varchar(10)  
    )  
    GO  
     
    CREATE TRIGGER dbo.employees_instead_of_update  
      ON dbo.employees  
      INSTEAD OF INSERT 
    AS 
      BEGIN 
        INSERT INTO dbo.employees (forename, surname, ssn)  
        SELECT forename  
             , surname  
             , NULL 
        FROM   inserted  
     
        /*--Alternative method  
        INSERT INTO dbo.employees (forename, surname)  
        SELECT forename  
             , surname  
        FROM   inserted  
        */  
      END 
    GO  
     
    --Single insert  
    INSERT INTO dbo.employees (forename, surname, ssn)  
      VALUES ('A''A''A')  
     
    --Multiple inserts  
    INSERT INTO dbo.employees (forename, surname, ssn)  
          SELECT 'B''B''B' 
    UNION SELECT 'C''C''C' 
     
     
    SELECT *  
    FROM   dbo.employees  
     
    GO  
    DROP TABLE dbo.employees 

    George
    • Marked as answer by Yum64147 Thursday, February 5, 2009 3:44 PM
    Thursday, February 5, 2009 3:36 PM
    Answerer
  • Yes. Thank you, Thank you, Thank you.

     

    Thursday, February 5, 2009 3:44 PM