cursor in trigger

• SQLRep

  SSCrazy

  Points: 2052

  More actions

  September 21, 2007 at 4:07 pm

  #118282

  Hi,

  I have question, about can i have cursor in trigger!!

  I want to but want some direction:

  Trigger is as follow: but if fails when buls update is being done table: so i though to put cursor but do know where to put!!

  alter TRIGGER [dbo].[test]

   

  ON [dbo].[test]

   

  FOR UPDATE, INSERT

   

  AS

  Should i put cursor here to check each testkey enter or updated then go furthur!!

  declare @key int, @ikey int

  select @ikey = i.testkey from inserted i

  if @ikey in (select distinct testkey from testaudit)     -- check if the data exists in audit table

  update testAudit                                            -- Update timestamp if data exist

  set [timestamp] = getdate()

  where testkey = @ikey

   

   

  else                                                              -- Insert new if data does not exists in audit table..

   

  INSERT testAudit (testKey)

  select testkey

  from inserted

• Jeff Moden

  SSC Guru

  Points: 1004363

  More actions

  September 21, 2007 at 6:01 pm

  #735399

  First, no need for a cursor in triggers... ever!  You can simply join to either the INSERTED table, the DELETED table, or both.

  Second, I recommend that you do NOT log inserts to a table.  You already know what the original data is by looking at the table you just inserted to.  All you're doing by logging the inserts is doubling the amount of data you're storing.  Just log the changes to the data.

   

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)