SQL: Is it possible to block a table insert just prior to the completion of a transaction?

Question

TL;DR: My real question is in the title, is it possible to block a table insert just prior to the completion of a transaction, that is, only concerning the data as it would be right before the transaction would be committed?

UPDATE: What procedes is merely a contrived example, possibly not a good one, demonstrating that I was unable to come up with a way to block an insertion/update prior to a transaction completing which contains two statements. I simply want to know if there is a way to do this, and the example is somewhat irrelevant.

The (possibly bad) example:

I am trying to prevent a transaction from occurring if some property of two tables is broken, for a simple example let's say I want to block if one of the first table's values (say ID) already exists in table 2.

create table dbo.tbl1
(
    id int,
    name varchar(20)
)

create table dbo.tbl2
(
    id int,
    name varchar(20)
)
go

The thing that I want to fail is the following:

begin transaction
    insert into tbl1 values(1, 'tbl1_1')
    insert into tbl2 values(1, 'tbl2_1')
commit transaction

Since at the end of the transaction the first table would have an id with the same value as that in table 2.

But unfortunately I've tried defining both a trigger to block this and a check constraint, and neither seems to block it.

Trigger (as suggested here):

CREATE TRIGGER MyTrigger ON dbo.tbl1
AFTER INSERT, UPDATE
AS

if exists ( select * from tbl2 inner join inserted i on i.id = tbl2.id)
begin
    rollback
    RAISERROR ('Duplicate Data', 16, 1);
end

Check Constraint (as suggested here):

create function dbo.tbl2WithID(@ID int) returns int
as
begin
    declare @ret int
    select @ret = count(*) from tbl2 where id = @ID
    return @ret
end
go

alter table dbo.tbl1 
add constraint chk_notbl2withid 
check (dbo.tbl2WithID(id) = 0)
go

How can I update my code to succesfully block the transaction? Do I need to redefine the transaction to be same time?

Answer 1

No, it's not possible to do what you want in MSSQLSever, but it might be in PostGres or Oracle.

Reason part 1: It's not possible to insert to two different tables in the same statement.

Reason part 2: "SQL Server [does not] allow constraint violations in a transaction as long as the transaction has not been committed yet."

Therefore it is not possible in SQLServer to have a single constraint for table insertion of more than one table that will block just prior to the completion of arbitrary transactions.

It's also worth mentioning that what you want is called a deferrable constraint. See more about that here.