Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

    Unanswered: Trigger on insert

    Is it possible to pick up the value that is being inserted within the scope of a trigger? For example, if I were to run the following
    Code:
    INSERT INTO people (firstname, lastname)
    VALUES ('George', 'V')
    Would it be possible to access these values in a trigger before they are inserted?
    Code:
    CREATE TRIGGER trigger1
      ON people
      FOR INSERT
    AS
      IF EXISTS (SELECT 1 FROM table1 WHERE firstname = <the value being inserted>) BEGIN
        Print '1'
      END
      ELSE BEGIN
        Print '2'
      END
    GO
    George
    Home | Blog

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Are you not aware of the the INSERTED and DELETED virtual tables? I think they will do what you need.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by blindman
    Are you not aware of the the INSERTED and DELETED virtual tables?
    I was not, but I am investigating them as we speak!
    ...
    ...
    ...
    Code:
    CREATE TABLE people (
        firstname char(6)
      , lastname  char(1)
    )
    GO
    
    CREATE TRIGGER trigger1
      ON people
      FOR INSERT
    AS
      DECLARE @firstname char(6)
      SET @firstname = (SELECT firstname FROM inserted)
      IF EXISTS (SELECT 1 FROM people WHERE firstname = @firstname) BEGIN
        Print @firstname + ' exists'
      END
      ELSE BEGIN
        Print @firstname + ' does NOT exist'
      END
    GO
    
    SET NOCOUNT ON
    INSERT INTO people (firstname) VALUES ('George')
    Print 'First insert complete'
    Print '---------------------'
    INSERT INTO people (firstname) VALUES ('George')
    Print 'Second insert complete'
    Print '---------------------'
    SET NOCOUNT OFF
    GO
    
    DROP TRIGGER trigger1
    DROP TABLE people
    Does not appear to work as expected...
    Code:
    George exists
    First insert complete
    ---------------------
    George exists
    Second insert complete
    ---------------------
    I can't see the wood for the trees today

    EDIT: Duh! The INSERTED table contains values that its name suggests - they're already in there!
    I don't think this is exactly what I want then
    Last edited by gvee; 10-22-07 at 09:43.
    George
    Home | Blog

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    your trigger also assumes you are inserting 1 record at a time and this may not always be the case. ooops. conference call time.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    That's something I am aware of; but it's a very good point to raise for future readers of this thread.

    Thanks Sean.
    George
    Home | Blog

Posting Permissions

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