Trigger to prevent deleting of last child row

[garwain]

I have a project where I have a 2 tables that are linked together, and need to put in a restriction at the database level (boss doesn't want code duplicated in multiple interfaces). Basically I have to enforce that there is always at least one child line, an prevent the delete operation if it removes the last child row. I used an ondelete trigger, which appeared to work perfectly at first, but now appears to work a little too well. It also prevents deleting the child line when the parent row cascades a delete operation, which should be allowed.

Here is a very simple sql code template that illistrates the problem

create table T1(id int  NOT NULL)
ALTER TABLE dbo.T1 ADD CONSTRAINT PK_T1 PRIMARY KEY CLUSTERED (	id)

create table T2(id int, T1_id int)



ALTER TABLE dbo.T2 ADD CONSTRAINT
	FK_T2_T1 FOREIGN KEY
	(
	T1_id
	) REFERENCES dbo.T1
	(
	id
	) ON DELETE CASCADE
	
GO

CREATE TRIGGER t_t2_before_delete ON [dbo].[T2] 
FOR DELETE 
AS
if not exists (select * from T2 where t1_id in (select t1_id from deleted))
begin
 RAISERROR ('Delete not allowed', 16, 1)
  rollback
end

go

insert into T1 values(1)
insert into T2 values(1,1)
insert into T2 values(2,1)
insert into T2 values(3,1)


delete from t2 where id=3 -- Delete works fine
delete from t2 --Delete fails as expected

delete from T1 --also fails, but needs to be allowed

How can I manage to put in a check so that I can still delete the row from T1, and through the relationship, delete all child rows in T2? I still need to block a delete performed directly on T2 if it contains the last row related to a row in T1.

Thanks

 

[Crowley16]

you can't...

setup a stored procedure(s) to update/delete records and tell everyone to use the stored procedure(s)

--------------------
Procrastinate Now!