Post reply

Trigger Read result from Stored Procedures

  • May 10, 2010 at 1:17 am

    #156937

    Hi,

    I've a Stored to process a Job.

    There is an INSERT INTO "Table1" ( Pkey,Value) values ( pkey1, value1 ) . In this table there is a Trigger "on insert" with "select pkey from Inserted"

    How can I set a variable @var = "pkey" from the trigger result ?

    There is no Identity fields to use @@Identity

    Thanks

  • May 10, 2010 at 1:28 am

    #1164858

    Could you explain a bit more what you're trying to do here? What does the trigger do?

    As for getting the output of a data modification, check out the OUTPUT clause.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 10, 2010 at 1:45 am

    #1164866

    OK,

    I've a Cursor, I read block input records to insert data into my DB ( from external data ).

    When I perform the Insert Into I need insert data using a function that automatically generates the new primary keys ( custom function ) so I need read this value from trigger to use it after.

    The triggers are all as

    "Select PKEY from inserted "

    Using OUTPUT i have to create a #Temptable or is possible assign directly a return value in a @Var ?

    many Thanks

  • May 10, 2010 at 2:13 am

    #1164873

    Why are you using a cursor?

    A trigger that just does a select from a table is a bad idea and generally frowned upon. Triggers should not return resultsets to the client (where it will go). I suggest you drop the triggers and have a look at the OUTPUT clause that I mentioned. Check Books Online for the usage.

    See if you can get rid of the cursor and do this as a set-based operation. I'm willing to bet you can, but without seeing the code I can't say more.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 10, 2010 at 2:22 am

    #1164875

    Thanks.

    I cannot drop triggers used in others areas .... but I can use the OUTPUT clausa. I've tried and works fine.

    There is a cursor because I should read and elaborate a lot of records in input....

    very good suggestion

  • May 10, 2010 at 2:37 am

    #1164877

    Gianni Ottimista (5/10/2010)

    Thanks.

    I cannot drop triggers used in others areas

    Just take that select out. Triggers should not return resultsets

    There is a cursor because I should read and elaborate a lot of records in input....

    That's not a good reason. Cursors are slow.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply