Are the users executing these statements directly against the database? I would think if you're controlling access to these operations via a stored procedure, you could bake in the logic you describe into the procedure without having the additional headache of maintaining DML triggers.
Maybe I just don't understand what you need exactly, but I'd do something like this, where I have procedures which can be invoked to control all the DML operations.
use Tempdb
go
set nocount on
go
/*****************************
Set up test harness
*****************************/
if object_id('tempdb.dbo.TableA') is not null drop table TableA
go
create table dbo.TableA
(
UserID int,
JobID int
primary key clustered (UserID, JobID)
)
if object_id('tempdb.dbo.TableB') is not null drop table TableB
go
create table dbo.TableB
(
UserID int,
JobID int,
SomeOtherID int
primary key clustered (UserID, JobID, SomeOtherID)
)
go
-- Insert proc
if exists (select 1 from sys.objects where object_id = object_id('tempdb.dbo.InsertRecord')) drop proc dbo.InsertRecord
go
create proc dbo.InsertRecord
@user-id int, @JobID int
as
begin tran
insert into dbo.TableA(UserID, JobID)
select @user-id, @JobID
insert into dbo.TableB(UserID, JobID, SomeOtherID)
select @user-id, @JobID, 1
commit tran
go
--Delete proc
if exists (select 1 from sys.objects where object_id = object_id('tempdb.dbo.DeleteRecord')) drop proc dbo.DeleteRecord
go
create proc dbo.DeleteRecord
@user-id int, @JobID int
as
begin tran
delete from dbo.TableA
where UserID = @user-id and JobID = @JobID
delete from dbo.TableB
where UserID = @user-id and JobID = @JobID
commit tran
go
/*****************************
Execute these a couple times
*****************************/
-- Set up a user and a job
exec dbo.InsertRecord 1, 1
select * from dbo.TableA
select * from dbo.TableB
--Delete that user and job
exec dbo.DeleteRecord 1, 1
select * from dbo.TableA
select * from dbo.TableB
--insert a new job
exec dbo.InsertRecord 1, 2
select * from dbo.TableA
select * from dbo.TableB
/*****************************
Cleanup
*****************************/
if object_id('tempdb.dbo.TableA') is not null drop table TableA
if object_id('tempdb.dbo.TableB') is not null drop table TableB