January 12, 2015 at 10:13 am
I have a table (Table A) where users input items with their corresponding job numbers. Sometimes users will make a mistake when creating a new record in Table A where the item does not have the correct job number. So they end up deleting the incorrect record and creating a new record with the correct item for the job number. I currently have a trigger on Table A that inserts a sister record into Table B. The down fall of that is when users make their corrections in Table A (delete incorrect record and add new correct record) it creates a record duplicate record in Table B, duplicate job numbers with different item numbers.
My goal is to create an insert/update trigger on Table A.
The trigger should perform the following tasks any time a new record is added to Table A...
- Search Table B for Duplicate job numbers.
- Delete duplicate jobs numbers in Table B
- Update the item and job number in Table B to match the correct item, job number in Table A
I've tried everything i can possibly think of an no solution. Any advice is appreciated. Thanks!
January 12, 2015 at 12:59 pm
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply