insert trigger - help

• carlos cachulo-318532

  Mr or Mrs. 500

  Points: 585

  May 9, 2019 at 10:35 pm

  #3639448

  Helllo

  I need whenever a record is inserted on a table it duplicates on the same table, nust changes  data on fields

  This o my table

  REFER  |  REF | DESIGN

   

  when i insert for ex.   1, 2, teste  it shoud insert another line with   2,1,teste

   

  I´ve tried with this trigger but no good, it enters on loop

   

  -- trigger on table

  <hr />

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  alter TRIGGER [dbo].[ALB_CatalogoMS_Insert]

  ON [dbo].[CatalogoMS]

  AFTER INSERT

  AS

  BEGIN

  DECLARE

  @cnt int,

  @total int,

  @Refer char(18),

  @Ref char(18),

  @Design varchar(60),

  @CalcSatmp char(15)

  SELECT @Refer=Refer, @Ref=Ref, @Design=Design FROM INSERTED (nolock)

  print @Refer

  set @cnt=1

  set @total=1

  while (@cnt < = @total)

  begin

  Insert into CatalogoMS (Refer,Ref,Design)

  VALUES (@ref,@refer,@Design)

  end

  END

   

  <hr />

   

   

   

   

   

• pietlinden

  SSC Guru

  Points: 62374

  May 9, 2019 at 10:56 pm

  #3639456

  Why not just create a stored procedure to accept two parameters and then do two inserts? Sounds to me like you're making this waaaay more complicated than it should be.

  CREATE PROC insertFlip 
     @A CHAR, @B CHAR
  AS 
  BEGIN
       INSERT INTO MyTable (A, B) VALUES (@A, @B);
      INSERT INTO MyTable (A, B) VALUES (@B, @A);
  END
  

• nigel.

  SSChampion

  Points: 11481

  May 21, 2019 at 2:51 pm

  #3645740

  Firstly I'd suggest reading this: https://www.brentozar.com/archive/2019/05/the-silent-bug-i-find-in-most-triggers/ , as you seem to have fallen into the trap that Brent describes.

  • This reply was modified 2 months ago by  nigel..

  --
  Nigel