THROW ERROR Still Executes Query And Inserts Data Into Table

Question

Basically, I am trying to compare a date the user will enter and if it is greater than todays date (GETDATE()), then it throws the error and doesn't enter the data. The query throws the error but it still enters the data with the result at the bottom.

USE EMR
GO

IF EXISTS (SELECT DB_ID('CheckDate'))
    DROP TRIGGER CheckDate
    GO

CREATE TRIGGER CheckDate
ON VISIT
AFTER INSERT, UPDATE
AS
BEGIN TRAN
    DECLARE @ErrorMessage VARCHAR(200)
    DECLARE @Date VARCHAR(20) = CAST ((SELECT CONVERT (DATE, GETDATE())) AS VARCHAR(20))
    SET @ErrorMessage = 'Date Must Be On Or Before ' + @Date + '';

    DECLARE @CheckDate DATE = (SELECT Date_Of_Service FROM inserted);

    IF CAST((@CheckDate) AS DATE) <= CAST(GETDATE() AS DATE)
        COMMIT TRAN

    ELSE
        RAISERROR(@ErrorMessage, 1, 1)

This is my insert statement:

INSERT INTO VISIT (PK_VISIT_ID, Date_Of_Service)
VALUES (02913, '2018-12-03')

And get this:

Date Must Be On Or Before 2016-02-17
Msg 50000, Level 1, State 1

(1 row(s) affected)

Answer 1

You are raising error with severity = 1 which means to server that it's just info message.

Check out replies to this post: TSQL: Prevent trigger suppressing error but rolling back transaction
There is also a link to severity table on msdn.

Answer 2

Your code is giving doing insert even after raiserror because trigger for insert runs the trigger after the rows are inserted.You can use check constraint or instead of insert as follows:

alter trigger tr2 on bb
instead of insert
as
begin
    begin try
        begin transaction
            if exists(select * from inserted where date1  > getdate())
                    begin
                        raiserror('date greater then today''s date',16,1)
                    end

                else
                    begin
                        insert into bb
                        select * from inserted
                        if @@trancount > 0
                        commit transaction
                    end
    end try

    begin catch
        declare @msg varchar(100) = error_message()
            if @@trancount > 0
                begin
                    raiserror(@msg,16,1)
                    rollback transaction
                end         
    end catch
end