Results 1 to 4 of 4
  1. #1
    Join Date
    May 2003
    Location
    Valencia - Spain
    Posts
    7

    Unanswered: Help On Trigger For Delete

    I have a SQL statement that deletes a lot of records in a table (PACCESOS_DET) and a Trigger that fires for delete on the table.
    The Trigger works fine when only one record is deleted but no when more than record is deleted; it only works for 1 and there is no error message.
    For each row deleted I need to update a column in another table (PACCESOS_CAB).
    This the trigger...

    CREATE TRIGGER ActualizaDiasVisita ON dbo.PACCESOS_DET
    FOR DELETE
    AS
    declare @mdias as int
    declare @mFKFeria as int
    declare @mtipo as char(1)
    declare @mfkcontacto as varchar(7)

    if exists( select * from PACCESOS_CAB m join deleted i on m.FKFeria= i.FKFeria and m.FKContacto=i.FKContacto and m.Tipo=i.Tipo)
    begin
    select @mfkferia=m.fkferia, @mfkcontacto = m.fkcontacto, @mtipo = m.tipo, @mdias = diasvisita from PACCESOS_CAB m join deleted i on m.FKFeria= i.FKFeria and m.FKContacto=i.FKContacto and m.Tipo=i.Tipo
    update PACCESOS_CAB set diasvisita = @mdias -1 where FKFeria= @mFKFeria and FKContacto=@mFKContacto and Tipo=@mTipo
    end




    Thanks in advanced.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    "Inside every large program is a small program screaming to get out."

    Your trigger is MUCH too complicated. Best I can figure, this is all you need:

    Code:
    CREATE TRIGGER ActualizaDiasVisita ON dbo.PACCESOS_DET
    FOR DELETE
    AS
    
    begin
    update	PACCESOS_CAB
    set	PACCESOS_CAB.diasvisita = PACCESOS_CAB.diasvisita -1
    from	PACCESOS_CAB
    	inner join deleted
    		on PACCESOS_CAB.FKFeria = deleted.FKFeria
    		and PACCESOS_CAB.fkcontacto = deleted.fkcontacto
    		and PACCESOS_CAB.Tipo = deleted.Tipo
    end
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    May 2003
    Location
    Valencia - Spain
    Posts
    7

    Talking Help on trigger for delete - solutioned

    It worked fine, sinco I keep on not Knowing the problem with the first solution.

    anyway, lot of thanks blindman.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Problems with your first solution:

    This phrase is completely unnecessary, as records between the two tables will be matched in the UPDATE query:

    "if exists( select * from PACCESOS_CAB m join deleted i on m.FKFeria= i.FKFeria and m.FKContacto=i.FKContacto and m.Tipo=i.Tipo)"

    These variables are not required, because as you can see from the solution all the records can be updated simultaneously with a single UPDATE statement, so it is not necessary to store values in temporary variables:

    "declare @mdias as int
    declare @mFKFeria as int
    declare @mtipo as char(1)
    declare @mfkcontacto as varchar(7)"

    This statement fails when more than one record is deleted, because your FROM clause will return more than one record, and the parameters can hold only single values:

    "select @mfkferia=m.fkferia, @mfkcontacto = m.fkcontacto, @mtipo = m.tipo, @mdias = diasvisita from PACCESOS_CAB m join deleted i on m.FKFeria= i.FKFeria and m.FKContacto=i.FKContacto and m.Tipo=i.Tipo"
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •