Instead of Delete trigger
An INSTEAD OF trigger is not the best solution here. An INSTEAD OF trigger performs no actions against the database unless explicitly done so in the trigger code. Your example will never delete rows regardless of the STATUS value because it contains no DELETE statement. The ROLLBACK is superfluous since no modifications were made.

Consider an AFTER DELETE trigger for this requirement. As with any trigger, multiple rows may be affected so it is important to handle such a case. Avoid using the select @caseid as that will identify only a single cid value even if different ones were deleted.

Here's an example of an after trigger for your need.

CREATE TRIGGER LOAD_DROPID_DEL on WMWHSE2.table2
FOR DELETE
AS
IF EXISTS(SELECT * FROM deleted where deleted.STATUS &lt;&gt;'5')
BEGIN
        RAISERROR ('Deletions not allowed after loaded ', 16, 1);
        ROLLBACK TRANSACTION;
END;
GO