Trigger to detect whether DELETE or UPDATE is called by stored proc

I have a scenario where certain users must have the rights to update or delete certain records in production. What I want to do put in a safeguard to make sure they do not accidentally update or delete a large section (or entirety) of the table, but only a few records as per their need. So I wrote a simple trigger to accomplish this.

CREATE TRIGGER delete_check
ON dbo.My_table
AFTER UPDATE,DELETE AS  
BEGIN 
    IF (SELECT COUNT(*) FROM Deleted) > 15
    BEGIN
        RAISERROR ('Bulk deletes from this table are not allowed', 16, 1)
        ROLLBACK
    END  
END --end trigger 

But here is the problem. There is a stored procedure that can do bulk updates to the table. The users can and should be allowed to call the stored procedure, as it’s scope is more constrained. So my trigger would unfortunately preclude them from calling the stored proc when they need to.

The only solution I have thought of is to run the stored proc as an impersonated user, then modify the trigger to exclude that user from the logic. But that will bring up other issues in my environment. Not unsurmountable, but annoying. Nevertheless, this seems the only viable option.

Am I thinking about this the right way, or is there a better approach?

Answer

You can add a check of @@NESTLEVEL in the trigger. The value will be 1 for an ad-hoc statement or 2 when called from the stored procedure.

CREATE TRIGGER delete_check
ON dbo.My_table
AFTER UPDATE,DELETE AS  
BEGIN 
    IF (SELECT COUNT(*) FROM Deleted) > 15
        AND @@NESTLEVEL = 1 --ad-hoc delete
    BEGIN
        RAISERROR ('Bulk deletes from this table are not allowed', 16, 1);
        ROLLBACK;
    END;  
END;