Trigger For Delete - Multiple Row Value

• ard_dicted

  SSC-Addicted

  Points: 445

  More actions

  April 25, 2010 at 4:42 am

  #1157377

  vidya_pande (4/24/2010)

  ---

  this is awesome support Kingston:-)

  yeah awesome, thank you so much again 🙂

  Heh... that'll probably get you fired because of how slow it is. It's also really, really bad advice. One of the worst places to use a cursor or While Loop or any form of RBAR is in a trigger.

  thx, i'll remember that

• lowfatexpert

  SSC Rookie

  Points: 27

  More actions

  April 25, 2010 at 5:57 am

  #1157380

  i think this is what you require

  needs to be in a before delete trigger

  update summary

  set countusedtogether=countusedtogether-n

  from summary as s

  inner join (

  select a.iditem as i1

  ,b.iditem as i2

  ,count(*) as N

  from detailtrans as a

  inner join deleted as b

  on a.idtrans =b.idtrans

  and a.iditem<>b.iditem

  group by a.iditem,b.iditem

  ) as x

  on s.iditem1=i1

  and s.iditem2=i2

• martinimaurilio

  SSC Rookie

  Points: 25

  More actions

  May 18, 2010 at 8:16 am

  #1168838

  [font="Courier New"]

  Hi

  I've a question about this post : what if I need to process

  data before proceeding to delete/update/insert?

  this is a short-version of a INSTEAD OF UPDATE trigger ( 1st example ) :

  .................................

  declare @ID int

  declare @ID_ADDR_AZIENDA int

  -- not using join between table MY_TABLE with INSERTED, needing processing data

  select @ID = ID, @ID_ADDR_AZIENDA = ID_ADDR_AZIENDA FROM INSERTED

  -- data processing ( simple version )

  if @ID_ADDR_AZIENDA <= 0

  set @ID_ADDR_AZIENDA = null

  -- now update table

  UPDATE MY_TABLE

  SET ID_ADDR_AZIENDA = @ID_ADDR_AZIENDA where (ID = @ID )

  this is a short-version for a DELETE trigger ( 2st example ) :

  .................................

  DECLARE @VALORE_OLD VARCHAR(255)

  DECLARE @VALORE_NEW VARCHAR(255)

  DECLARE @key INT

  -- reading differents data from DELETED

  SET @key = (SELECT ID_PROG_AZIENDA FROM DELETED)

  SET @UTENTE_OLD = (SELECT UTENTE FROM DELETED)

  SET @DATA_OLD = (SELECT DATA_VARIAZIONE FROM DELETED)

  SET @ID_STUDIO_PROC_OLD = NULL

  -- if a condition occures, it executes a stored procedure

  IF NOT ( ISNULL((SELECT ID_AZIENDA FROM INSERTED),0) =

  ISNULL((SELECT ID_AZIENDA FROM DELETED),0) )

  BEGIN

  SET @VALORE_NEW = (SELECT ID_AZIENDA FROM INSERTED)

  SET @VALORE_OLD= (SELECT ID_AZIENDA FROM DELETED)

  EXEC MY_STORED_PROCEDURE @key, @VALORE_NEW,

  @VALORE_OLD

  END

  .................................................

  other stored-procedure calls

  Is there a solution to manage multiple records for this situations?

  Thanks in advance

  [/font]