Instead of delete trigger and Entity Framework

Question

Hi everybody,

I found the following trigger on our table:

ALTER trigger [dbo].[prof_ctr_delete_DontDeleteUsed]
on [dbo].[prof_ctr]
instead of delete
as
begin
   -- Version 1
   declare @bErrorFlag bit = 0
   set nocount on
   if exists (select 1
              from items i join deleted d on i.pr_ctr_1 = d.pr_ctr_no
                                          or i.pr_ctr_2 = d.pr_ctr_no
                                          or i.pr_ctr_3 = d.pr_ctr_no
                                          or i.pr_ctr_4 = d.pr_ctr_no
                                          or i.pr_ctr_5 = d.pr_ctr_no
                                          or i.pr_ctr_6 = d.pr_ctr_no
              )
      begin
         Set @bErrorFlag = 1
         raiserror('Cannot delete profit centers that are currently in use on items.', -1, -1)
      end

    if @bErrorFlag = 0
       delete p from prof_ctr p join deleted d on p.pr_ctr_no = d.pr_ctr_no
end

First question - is it written OK? 

And secondly, what I found is that this error doesn't seem to be returned back. We are using Entity Framework and the delete method in our repository is the following:

 public virtual void Delete(T entity)
        {
            EntityState entityState = _siriusContext.GetEntityState(entity);
            //if (entityState != EntityState.Deleted)
            //{                
            //        _siriusContext.SetDeleted(entity);
            //}
            //else
            //{
            //    _dbSet.Attach(entity);

            if (entityState==EntityState.Detached)
            {
                _dbSet.Attach(entity);
            }

            //https://stackoverflow.com/questions/19325473/ef6-0-the-relationship-could-not-be-changed-because-one-or-more-of-the-foreign
            _dbSet.Remove(entity);
            //}

            try
            {
                SaveChanges();
            }
            catch (DbUpdateException ex)
            {
                _exceptionParser.ThrowCustomDeleteException(ex, TriggerType.Delete);
            }
        }

So, do you see what is wrong in the above implementation (both T-SQL and C#)? The behavior we're seeing is that front-end reports that the row was successfully deleted (but, luckily, nothing was really deleted).

Thanks a lot in advance.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

My TechNet articles

Answer 1

raiserror('Cannot delete profit centers that are currently in use on items.', -1, -1)

Hello Naomi,

Serverity & state are negativ values, this results in a kind of info message, but not in a real error, see RAISERROR (Transact-SQL)

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 2

Yes, I was thinking the problem may be with state and severity. I'll change that and I also plan to add the check directly in the code before deletion.

Thanks.

UPDATE. Changed the trigger (although I was getting generic error in front-end) and then also added a check for 'CanBeDeleted'. Now it all works as desired.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

My TechNet articles