Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2002

    Unanswered: passing variables from user procedure to trigger

    Hi all,

    In a trigger I want to refer to information (variables) wich I use in a userprocedure wich is responseble for the insert, update or delete statement that triggerd the trigger.


  2. #2
    Join Date
    Nov 2002


    This is imposible by the very nature of triggers. Triggers are supposed to be independant. However, if it is badly required you could create yourself a table to hold any values you need to pass into your trigger. The trigger can then interigate the table.

    If you need to make sure the line in the table is unique to that connection, put in the SPID. This is available to both the stored procedure and the trigger.

    Be careful. This may not work if you use the procedure so often that two lines could be added to the variable table.


    In the Database:

    CREATE TABLE ##tmp_variables (spid smallint, myvariable1 varchar(255))

    Stored Proc:

    DECLARE @MyVar1 varchar(255)

    -- Do some work...

    -- Add variable to table
    INSERT INTO ##tmp_variabels (@@SPID, @MyVar1)

    -- Do something to the table with trigger on
    UPDATE trigger_table SET something = 'somethingelse'


    -- Get the variables
    DECLARE @MyVar1 varchar(255)

    SELECT @MyVar1 = myvariable1 FROM ##tmp_variabels WHERE spid = @@SPID

    -- IMPORTANT: Clean the variable table after use
    DELETE FROM ##tmp_variabels WHERE spid = @@SPID

    -- Do some work...

Posting Permissions

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