Archiving Multiple Records using a Delete Trigger

Question

I'm surprised this hasn't been addressed yet but I trawled through stackoverflow and can't find it.

Basically, I'm trying to enable mass deletions of student accounts in the system but would like these records to be archived so that the records are still available, just no longer a part of the system.

I've written a delete trigger to automatically archive a deleted record into an archive table.

However, I don't know how to make the trigger accept handling multiple records, at least for an insert into statement. Right now, my insert into statement will only enter the first record of the in-memory deleted table into my archive table. I'm using SQL Server.

drop trigger trgDelete;

create TRIGGER trgDelete ON KPMG_URecruiting_CIS484_Group21.dbo.Student AFTER DELETE
as begin
	Declare @Email varchar (50);
	Declare @Password varchar(50);
	Declare @FirstName varchar(50);
	Declare @MiddleName varchar(50);
	Declare @LastName varchar(50);
	Declare @PreferredName varchar(50);	
	Declare @CellPhone char(20);
	Declare @Notes varchar(max);
	Declare @Institution varchar(100);
	Declare @EducationLevel char(20);
	Declare @Major varchar(100);
	Declare @Minor varchar(100);
	Declare @GPA float;
	Declare @GradDate date;
	Declare @CPA bit;
	Declare @Service varchar(100);
	Declare @Industry varchar(100);
	Declare @Location varchar(100);

	Select @Email=d.Email from deleted d;
	Select @Password=d.Password from deleted d;
	Select @FirstName=d.FirstName from deleted d;
	Select @MiddleName=d.MiddleName from deleted d;
	Select @LastName=d.LastName from deleted d;
	Select @PreferredName=d.PreferredName from deleted d;
	Select @CellPhone=d.CellPhone from deleted d;
	Select @Notes=d.Notes from deleted d;
	Select @Institution=d.Institution from deleted d;
	Select @EducationLevel=d.EducationLevel from deleted d;
	Select @Major=d.Major from deleted d;
	Select @Minor=d.Minor from deleted d;
	Select @GPA=d.GPA from deleted d;
	Select @CPA=d.CPA from deleted d;
	Select @GradDate=d.GradDate from deleted d;
	Select @Service=d.Service from deleted d;
	Select @Industry=d.Industry from deleted d;
	Select @Location=d.Location from deleted d;

	Insert into Student_Archive 
	(
		Email, Password, FirstName, MiddleName, LastName, PreferredName, CellPhone, Notes, Institution, EducationLevel, Major, Minor, GPA, GradDate, CPA, Service, Industry, Location, Audit_TimeStamp
	)
	Values 
	(
		@Email, @Password, @FirstName, @MiddleName, @LastName, @PreferredName, @CellPhone, @Notes, @Institution,@EducationLevel, @Major, @Minor, @GPA, @GradDate, @CPA, @Service, @Industry, @Location, getdate());

	end

Can you point me in how to force the trigger into performing the insert into for EACH record in the in-memory deleted table rather than just the first? Any advice much appreciated.

Answer 1

Well, just do the INSERTdirectly:

create TRIGGER trgDelete ON KPMG_URecruiting_CIS484_Group21.dbo.Student AFTER DELETE
as 
begin

    Insert into Student_Archive 
    (
        Email, Password, FirstName, MiddleName, LastName, PreferredName, CellPhone, Notes, Institution, EducationLevel, Major, Minor, GPA, GradDate, CPA, Service, Industry, Location, Audit_TimeStamp
    )
    SELECT Email, Password, FirstName, MiddleName, LastName, PreferredName, CellPhone, Notes, Institution, EducationLevel, Major, Minor, GPA, GradDate, CPA, Service, Industry, Location, Audit_TimeStamp, getdate()
    FROM DELETED;

end

Answer 2

Try this - just a nice, simple set-based approach:

CREATE TRIGGER trgDelete 
ON KPMG_URecruiting_CIS484_Group21.dbo.Student 
AFTER DELETE
AS BEGIN
    INSERT INTO dbo.Student_Archive (Email, Password, FirstName, MiddleName, LastName, PreferredName, CellPhone, Notes, Institution, EducationLevel, Major, Minor, GPA, GradDate, CPA, Service, Industry, Location, Audit_TimeStamp)
       SELECT
          Email, Password, FirstName, MiddleName, LastName, PreferredName, CellPhone, Notes, Institution, EducationLevel, Major, Minor, GPA, GradDate, CPA, Service, Industry, Location, SYSDATETIME()
      FROM
         Deleted
END