Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2004
    Location
    Germany
    Posts
    25

    Question Unanswered: Using inserted / deleted Tables with text / nText / image data type

    Hi folks,

    Table:

    a int,
    b int,
    c int,
    d text

    I need to change my AFTER - Trigger from this (example!):

    select * into #ins from inserted

    to something like

    select *(without Text / nText / image -columns) into #ins from inserted.

    So I tried to build a string like this: (using INFORMATIONSCHEMES)

    select @sql = 'select a,b,c into #ins from inserted'
    exec(@sql)

    a,b,c are not of Text, nText or Image datatype.

    After executing the trigger, I get an error, that inserted is unknown.

    Does anyone know how to solve this ?

    Thx.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I don't think that you can solve it directly. The inserted view only exists within the trigger itself, so you can't interrogate it via dynamic SQL (because it executes in a different context than the trigger does).

    You could probably create the temp table outside of the trigger, then populate it using static SQL from inside the trigger.

    -PatP

  3. #3
    Join Date
    Apr 2004
    Location
    Germany
    Posts
    25

    Question

    Is it possible to use an INSTEAD OF Trigger instead, and execute the statement that fired the trigger ?

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    It boils down to a question of what exists when. If you can create a permanent table, or a temp table that exists before the trigger fires you should be Ok. You may be able to make a global temp table work if you create it with dynamic SQL and populate it with static SQL, but I wouldn't even bet on that because the timing would be so sensitive.

    -PatP

  5. #5
    Join Date
    Apr 2004
    Location
    Germany
    Posts
    25

    Question

    Another Question:

    Is it possible to execute the original statement that fired an INSTEAD OF trigger from within the trigger ?

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I don't know of any way to access the statement that launched an instead of trigger. I don't think there is any syntactical way to "fall back" to the invoiking statement. While an extended stored procedure could access the token stream in the buffer and might be able to reconstruct the invoking statement, there isn't any way to invoke it dynamically because the INSTEAD OF trigger would still be running so it couldn't be dropped in order to allow the invoking statement without simply re-launching the INSTEAD OF trigger.

    -PatP

Posting Permissions

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