Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2002
    Location
    Netherlands
    Posts
    3

    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.


    Andrew

  2. #2
    Join Date
    Nov 2002
    Location
    England
    Posts
    4

    Imposible

    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.

    Example:

    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'

    Trigger:

    -- 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
  •