create table dbo.testXa
( a integer)
go
create trigger [dbo].[ti_test] on [dbo].[testXa] after insert
as raiserror 19999 'Sorry, not insertable'
go
select * from testxa
--emtpy table
insert into testxa values (123)
--we got an error
select * from testxa
--insert took place anyway
begin try
insert into testxa values (124)
end try
begin catch
SELECT 'Error in Trigger' AS error, Error_Message() AS MESSAGE
end catch
go
select * from testxa
--no insert took place
drop table dbo.testXa

CREATE PROCEDURE dbo.test
AS

--select 'test'
RAISERROR 19999 'test';


GO
DECLARE @retval INT

EXEC @retval = dbo.test

SELECT @retval

DROP PROCEDURE dbo.test

CREATE TABLE dbo.testXa
    ( 
    a integer
    )

CREATE TABLE dbo.TestXb
    (
    b tinyint
    )
GO

CREATE TRIGGER [dbo].[ti_test] ON [dbo].[testXa] 

after INSERT

AS 

INSERT INTO dbo.TestXb
    SELECT 
        * 
    FROM 
        inserted
   
GO

SELECT 
    * 
FROM 
    testxa

--emtpy table
INSERT INTO testxa 
    VALUES 
        (
        1234
        )

--we got an error, not raised an error so no insert
SELECT 
        * 
FROM 
    testxa

SELECT 
    * 
FROM 
    testxb

GO

BEGIN try
    INSERT INTO testxa 
        VALUES 
        (
        12345
        )
END try
BEGIN catch
    SELECT 
           'Error in Trigger' AS error, 
            Error_Message() AS MESSAGE
END catch
GO

--no insert took place
SELECT 
    * 
FROM 
    testxa

DROP TABLE dbo.testXa
DROP TABLE dbo.testXb

CREATE TRIGGER [dbo].[ti_test] ON [dbo].[testXa] 

after INSERT

AS 

    BEGIN try
        --will not work due to overflow
        INSERT INTO dbo.TestXB
            SELECT 
                * 
            FROM 
                inserted

   END Try
   BEGIN Catch
        DECLARE @error VARCHAR(500)
        SET @error = Error_Message()
        SELECT @error
        
   END Catch