Trigger after insert doesn’t work although all data is deleted

My table is empty and I’m trying to insert a record with the condition that the bit type column is not true, because the true condition can only be once for each country. Here is my trigger:

ALTER TRIGGER [dbo].[Trg_PreventDefaultDevise] 
ON [dbo].[RELDEVISEPAYS]
AFTER INSERT, UPDATE
AS 
BEGIN
    SET NOCOUNT ON;

    DECLARE @Pays varchar(2);
    DECLARE @Compte int = 0;

    --select @Default = Rel_Default from Inserted;

    SELECT @Pays = REL_PAYS FROM inserted
    PRINT @Pays

    SET @Compte = (SELECT COUNT(rel_pays) FROM inserted 
                   WHERE Rel_Pays = @Pays AND Rel_Defaut = 0)
    PRINT @compte;

    IF (SELECT COUNT(*) FROM RELDEVISEPAYS 
        WHERE REL_DEFAUT = 1 AND REL_PAYS = @Pays) >= 1
    BEGIN
        RAISERROR (N'Ce pays dispose déjà d''une devise par défaut', 16, 1)
        ROLLBACK
        RETURN
    END
END

Answer

This line select @Pays = REL_PAYS from inserted is making the classic SQL Server trigger mistake – which is assuming there will only be a single row in the Inserted psuedo-table – when in fact there could be many.

You don’t really need the Inserted table to carry out your test. Instead just check the real table for any duplicates and if there are rollback.

What I think you want is:

alter trigger [dbo].[Trg_PreventDefaultDevise] on [dbo].[RELDEVISEPAYS]
after insert, update
as
begin
    set nocount on;

    if exists (select 1 from dbo.RELDEVISEPAYS where REL_DEFAUT = 1 group by REL_PAYS having count(*) > 1)
    begin
        raiserror (N'Ce pays dispose déjà d''une devise par défaut',16,1);
        rollback;
    end;
end