Create trigger which would not allow to delete values in specific column?

Question

I've a simple table like this:

MARK {IdMark Int, IdStudent Int, Value Int, Subject Varchar(10)}

and I would like to create trigger which would not allow to delete rows in that table but there should be possible to alter values in column "Value" unless it's NULL.

The code below does not work like I would like to at all:

CREATE TRIGGER delValue
ON mark
FOR INSERT, UPDATE, DELETE
AS
IF EXISTS(SELECT 1 FROM inserted i JOIN deleted d ON i.IdMark = d.IdMark WHERE i.IdMark IS NULL)
    BEGIN
        RAISERROR('You can't delete marks!', 16, 1)
        ROLLBACK
    END

Answer 1

Try the following:

You only need to check inserted for null values. And if there is nothing in inserted but something in deleted then its a delete.

Also, watch out when using single quotes in a message, you need to escape them (by repeating them).

CREATE TRIGGER delValue
ON mark
FOR INSERT, UPDATE, DELETE
AS
begin
  -- If attempting to set to null, rollback
  IF EXISTS (SELECT 1 FROM inserted WHERE IdMark IS NULL) BEGIN
    RAISERROR('You can''t set marks to null!', 16, 1);
    ROLLBACK;
  END
  -- If attempting to set to delete, rollback
  -- There will never be anything in inserted for a delete
  IF NOT EXISTS (SELECT 1 FROM inserted) and EXISTS (SELECT 1 FROM Deleted) BEGIN
    RAISERROR('You can''t delete marks!', 16, 1);
    ROLLBACK;
  END
end