locked
Instead of delete trigger and Entity Framework RRS feed

  • 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

    Saturday, July 21, 2018 12:28 AM

Answers

  • 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]

    • Proposed as answer by Visakh16MVP Saturday, July 21, 2018 6:37 AM
    • Marked as answer by Naomi N Monday, July 23, 2018 12:16 PM
    Saturday, July 21, 2018 4:00 AM

All replies

  • 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]

    • Proposed as answer by Visakh16MVP Saturday, July 21, 2018 6:37 AM
    • Marked as answer by Naomi N Monday, July 23, 2018 12:16 PM
    Saturday, July 21, 2018 4:00 AM
  • 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


    • Edited by Naomi N Monday, July 23, 2018 12:17 PM
    Sunday, July 22, 2018 12:38 PM