insert trigger - help

  • carlos cachulo-318532

    Mr or Mrs. 500

    Points: 585

    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: 62419

    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: 11576

    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 4 months ago by  nigel..

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

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