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?

Why not check the values being passed in and not insert at all if the same value is going to be inserted to both tables? — shree.pat18, Nov 05 '15 at 02:14
What database is this? Your tags are all over the place. Is there any reason you cannot define a unique constraint on `tbl1`? — mustaccio, Nov 05 '15 at 02:16
@shree.pat18: my goal is to let people define any transactional statement involving the two tables but to be comfortable knowing they can't break the condition I've imposed. Just like we define constraints so we don't have to remember that logic each time we want to update the table. — user420667, Nov 05 '15 at 02:17
First you are inserting data into `tb1` followed by `tb2` Since you have defined trigger on `tb1`. There wont be matching record in `tb2` when data is inserted into `tb1` — Pரதீப், Nov 05 '15 at 02:19
@Sick: yes, my goal is to define the block so that it runs prior to the end of the transaction. That is I want to know how to define it so that it runs the check just before the transaction would complete. — user420667, Nov 05 '15 at 02:26
How about you check if the value exists in the table before you insert. If it exists, then don't insert The values and raise an error. — singhsac, Nov 05 '15 at 02:26
@singhsac: this is just an illustrative example. My goal is to be able to do this generally and not have to wory about the order of operations. My block could have been anything based on table 2. It could have even just been does any row exist in table 2 at all. — user420667, Nov 05 '15 at 02:28
@user420667 - Then define a trigger on `tb2` as well, But this is completely weird request — Pரதீப், Nov 05 '15 at 02:29
@Sick: I think perhaps the example is a little contrived but I don't see what's weird about wanting a way to be able to check the state of affairs prior to the completion of a transaction instead of with uncommitted writes. — user420667, Nov 05 '15 at 02:44

score 1 · Answer 1 · answered Nov 05 '15 at 04:21

1

Try changing the trigger to fire BEFORE the event as in:

CREATE TRIGGER MyTrigger ON dbo.tbl1
BEFORE INSERT, UPDATE -- this is changed to "BEFORE"
AS

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

NOTE:

A check constraint can only check WITHIN the table itself.

You should also study up on FOREIGN KEYS as this is exactly what would solve what you are running into.

answered Nov 05 '15 at 04:21

Walker Farrow

3,579
7
29
51

1) In t-sql there is no before for triggers. There are for and after (which are the same) and instead of. 2) It is not true that a check constraint can only check within the table itself, as clearly my check constraint checks outside its table. 3) foriegn keys are for when you want to ensure that something else exists in another table, not to ensure that something *doesn't* exist. – user420667 Nov 05 '15 at 17:41
All I had to do was change BEFORE to INSTEAD OF and that was it. :) Thanks. – Helder Gurgel Jun 28 '23 at 13:09

score 0 · Accepted Answer · edited May 23 '17 at 12:04

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.

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

2 Answers2