SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table prepod(id bigint identity, email nvarchar(300));
create trigger trg_prep on prepod after insert, update as
    if exists (select 1 from prepod p, inserted i where i.email = p.email and i.id <> p.id)
        begin
            raiserror('Oops', 16, 1);
            rollback tran;
            return;
        end;
    if exists (select 1 from prepod p, deleted d where d.email = p.email and d.id <> p.id)
        begin
            raiserror('Oops', 16, 1);
            rollback tran;
            return;
        end;
insert into prepod(email) values ('a@a'),('b@b');
2 rows affected

insert into prepod(email) values ('a@a');
Msg 50000 Level 16 State 1 Line 4
Oops
Msg 3609 Level 16 State 1 Line 1
The transaction ended in the trigger. The batch has been aborted.

update prepod set email = 'a@a' where email = 'b@b';
Msg 50000 Level 16 State 1 Line 4
Oops
Msg 3609 Level 16 State 1 Line 1
The transaction ended in the trigger. The batch has been aborted.

select * from prepod;


id
email


1
a@a

2
b@b