CODEWITHSUNDEEP
sqlsql-server
StringDotZ
StringDotZ

Reputation: 11

Separate rows in multiple delete SQL server

I have a trigger. When i delete something in my table i want to create a row in other table named Archive with the row i deleted. All works fine. The problem is when i delete more than one row in the same time. It store just the first row in the table Archive. I think about creating a loop but how i separate them in subqueries to select every row. How do i solve that?

ALTER TRIGGER [dbo].[trigArhiva]
   ON  [dbo].[student] 
   AFTER DELETE
AS 
SET NOCOUNT ON;
BEGIN
    declare @nume varchar(45);
    declare @anStudiu int;
    declare @prenume varchar(45);
    declare @CNP char(13);
    declare @grupa int;
    declare @idFacult int; 
    declare @rowss int;
    declare @i int;
    select @rowss = count(*) from DELETED;
    set @i = 1;

    while (@i <= @rowss) 
    begin

    select @nume = nume from DELETED;
    select @anStudiu = anStudiu from DELETED;
    select @prenume = prenume from DELETED;
    select @CNP = CNP from DELETED;
    select @grupa = idGrupa from DELETED;
    select @idFacult = idFacult from DELETED;

    insert into arhivaStud(nume, prenume, CNP, grupa, idFacult, anStudiu) values (@nume, @prenume, @CNP, @grupa, @idFacult, @anStudiu);

    set @i = @i+1;

    end
END

Upvotes: 0

Views: 40

Answers (1)

etsa
etsa

Reputation: 5060

I think you should use something like this (deleted is an "internal" representation of all records deleted during the operation which recall the trigger):

ALTER TRIGGER [dbo].[trigArhiva]
   ON  [dbo].[student] 
   AFTER DELETE
AS 
SET NOCOUNT ON;
BEGIN
INSERT INTO arhivaStud(nume, prenume, CNP, grupa, idFacult, anStudiu) 
SELECT nume, prenume, CNP, idgrupa, idFacult, anStudiu FROM deleted
END

Upvotes: 3

Related Questions