Delete Trigger for Audit Table SQL Server Database

Question

I am using Mircosoft SSMS for my sql server and ASP.NET CORE for my website.

The query below is my Delete Trigger. However, whenever I try to delete something in my website, this error pops up: "InvalidCastException: Unable to cast object of type 'System.Guid' to type 'System.Int32'."

Delete Trigger:

create trigger Branch_Delete
on Branch
after delete 
as
begin
set nocount on;
declare @BranchID uniqueidentifier
select @BranchID = deleted.BranchID
from deleted
insert into AuditLog(TableName, ModifiedBy, AuditDateTime , ID ,AuditAction)
Values
('Branch', SUSER_SNAME(), GETDATE(), @BranchID ,'Delete')
select BranchID from Deleted
end

Model:

namespace Test.Models
{
    public class BranchModel
    {
        [Key]
        [Display(Name = "Branch ID")]
        public Guid BranchID { get; set; }

        [Required(ErrorMessage = "Please Enter The Branch Name ..")]
        [Display(Name = "Branch Name")]
        public string BranchName { get; set; }

        [Required(ErrorMessage = "Please Enter The Branch Address ..")]
        [Display(Name = "Branch Address")]
        public string BranchAddress { get; set; }

    }
}

Controller (for delete function):

 public async Task<IActionResult> DeleteBranch(Guid? id)
        {
            if (id == null)
            {
                return NotFound();
            }

            var branchModel = await _context.Branch
                .FirstOrDefaultAsync(m => m.BranchID == id);
            if (branchModel == null)
            {
                return NotFound();
            }

            return View(branchModel);
        }

        // POST: BranchModels/Delete/5
        [HttpPost, ActionName("DeleteBranch")]
        [ValidateAntiForgeryToken]
        public async Task<IActionResult> DeleteConfirmedBranch(Guid id)
        {
            var branchModel = await _context.Branch.FindAsync(id);
            _context.Branch.Remove(branchModel);
            await _context.SaveChangesAsync();
            return RedirectToAction(nameof(Branch));
        }

Answer 1

Your trigger will only handle if there was only 1 row deleted. You need to change your trigger code to make it so that it can handle multiple row deletes too. Something like...

CREATE TRIGGER Branch_Delete
ON Branch
AFTER DELETE
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO AuditLog
        (
            TableName
          , ModifiedBy
          , AuditDateTime
          , ID            
          , AuditAction
        )
    SELECT
           'Branch'
          , SUSER_SNAME()
          , GETDATE()
          , BranchID
          , 'Delete'
    FROM    Deleted;
END;

Also make sure the data type for Column [ID] in table [AuditLog] matches the data type of [BranchID] in the [Branch] table.