June 8, 2011 at 4:48 pm
I am attempting to create a trigger that will check if a user is trying to save a duplicate invoice. Sometimes the duplicate saves are what is needed, so I don't want to disallow it completely. I'm having the trigger warn the user that this record may be a duplicate based on these values, but then save the record if that is what the user really wants. I'm doing this in a trigger (bad todd! no treat! :w00t:) because there is no centralized procedure in question and the front-end code is kind of locked away. So here is a mock-up of the scenario below. Any type of criticism is welcomed.
create table tblMiscBillingLog (
MiscBillingLogID int, JobsID int, MiscBillingLog varchar(100), VendorsID int, sngUnitsConsumed real, curRelativeCost money);
go
create table tblMiscBillingLog_DupWarnings (MiscBillingLogID int not null);
go
create trigger [dbo].[trg_tblMiscBillingLog_CheckForDuplicates] ON [dbo].[tblMiscBillingLog] instead of insert as
select * into #MBL_temp from inserted;
if exists (
select 1 from #MBL_temp i
inner join tblMiscBillingLog mbl
on i.JobsID = mbl.JobsID
and i.MiscBillingLog = mbl.MiscBillingLog
and i.VendorsID = mbl.VendorsID
and i.sngUnitsConsumed = mbl.sngUnitsConsumed
and i.curRelativeCost = mbl.curRelativeCost
and i.MiscBillingLogID <> mbl.MiscBillingLogID
where mbl.MiscBillingLogID not in (select MiscBillingLogID from tblMiscBillingLog_DupWarnings)
)
begin
raiserror('Duplicate entry warning: this invoice may already exist.', 16, 1)
insert into tblMiscBillingLog_DupWarnings (MiscBillingLogID)
select mbl.MiscBillingLogID
from tblMiscBillingLog mbl
inner join #MBL_temp i
on i.JobsID = mbl.JobsID
and i.MiscBillingLog = mbl.MiscBillingLog
and i.VendorsID = mbl.VendorsID
and i.sngUnitsConsumed = mbl.sngUnitsConsumed
and i.curRelativeCost = mbl.curRelativeCost
and i.MiscBillingLogID <> mbl.MiscBillingLogID
end
else
begin
insert into tblMiscBillingLog select * from #MBL_temp
end
go
--run the inserts separately:
insert into tblMiscBillingLog values (54264,1,'test1',1,1,1); -- clean insert
insert into tblMiscBillingLog values (54265,2,'test2',1,1,1); --clean insert
insert into tblMiscBillingLog values (54266,1,'test1',1,1,1); --should cause message and not be inserted
insert into tblMiscBillingLog values (54266,1,'test1',1,1,1); --should not cause message and be inserted
select * from tblMiscBillingLog --3 values
select * from tblMiscBillingLog_DupWarnings --should only have 54264
--clean up
drop table tblMiscBillingLog;
drop table tblMiscBillingLog_DupWarnings;
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
June 9, 2011 at 7:38 am
How does data get inserted? Directly from an app? Seems to me that by returning a severity 16 for a warning is going to cause the app to see it as an error and perform a rollback. It could get messy real quick. What is the app supposed to do when it receives the warning? Depending on how it's written, it may just get ignored altogether.
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 9, 2011 at 8:46 am
Mike01 (6/9/2011)
How does data get inserted? Directly from an app? Seems to me that by returning a severity 16 for a warning is going to cause the app to see it as an error and perform a rollback. It could get messy real quick. What is the app supposed to do when it receives the warning? Depending on how it's written, it may just get ignored altogether.
Yes, the data is inserted directly from the app. After running this on my test servers, it's not rolling the trans back on the raiserror and displays the warning message like expected.
Notice that this trigger is an INSTEAD OF INSERT trigger, so if the EXISTS is true, the actual record is not inserted.
I don't think I mention in the original post that the table tblMiscBillingLog is in the existing arcitechture but the tblMiscBillingLog_DupWarnings table and trigger are what I'm adding to have this feature.
It could get messy real quick.
Too late. :crying: I will have to edit the ELSE part since MiscBillingLogID is an identity in the actual table:
else
begin
set identity_insert tblMiscBillingLog on
insert into tblMiscBillingLog (<column list>) select <column list> from #MBL_temp
set identity_insert tblMiscBillingLog on
end
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
June 9, 2011 at 8:53 am
This came up in another thread recently, but I can't find it right now.
If it requires user interaction, it really belongs in the front end. Triggers are about as far from the front end as you can get.
The biggest potential problem that I remember is how do you distinguish between someone accidentally running the command twice and someone running the command to override the error. (This is a particularly big problem with web front ends where people are inclined to click a button again if they don't get an immediate response.)
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 9, 2011 at 9:11 am
drew.allen (6/9/2011)
If it requires user interaction, it really belongs in the front end. Triggers are about as far from the front end as you can get.
I agree, and I'm starting to hate every part of this code. I thought it would be a simple check where the fields are equal and raise and error, but I had to use an instead of insert and delay the insert and I'm not trusting that. How will it affect the behavior of the other triggers on the table, for example?
The biggest potential problem that I remember is how do you distinguish between someone accidentally running the command twice and someone running the command to override the error.
This problem exists even if implemented in the front-end. The users just need a "hey, you may have picked up the same paper again" message. If they still enter a dup, then that just has to be resolved in billing.
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
June 10, 2011 at 9:08 am
I think I have a solution I can live with. I removed the extra table to hold the ID's that have been tried already and instead I'm putting a datetime column on the base table to keep track of when the invoice duplicate attempt was tried. So before, once a warning was made, duplicates could be entered continuously without additional warnings. Now, the user can save the duplicate within 5 minutes of the warning. After that, an additional warning will appear.
So here is the current mock-up code. I'd appreciate you guys taking a look at it again. Thanks!
Changes from last code post:
-not using tblMiscBillingLog_DupWarnings to track IDs. Instead adding DupWarned to base table.
-made MiscBillingLogID an identity to model production system
-changed trigger to check for last dup update
-no longer checking for match on jobID in case user is mis-applying invoice
create table tblMiscBillingLog (
MiscBillingLogID int identity(50000,1), JobsID int, MiscBillingLog varchar(100), VendorsID int, sngUnitsConsumed real, curRelativeCost money);
go
alter table tblMiscBillingLog add DupWarned datetime;
go
create trigger [dbo].[trg_tblMiscBillingLog_CheckForDuplicates] ON [dbo].[tblMiscBillingLog] instead of insert as
if exists (
select 1 from inserted i
inner join tblMiscBillingLog mbl
on i.MiscBillingLog = mbl.MiscBillingLog--user puts the vendor's invoice # here
and i.VendorsID = mbl.VendorsID
and i.sngUnitsConsumed = mbl.sngUnitsConsumed
and i.curRelativeCost = mbl.curRelativeCost
and i.MiscBillingLogID <> mbl.MiscBillingLogID
where datediff(mi,isnull(mbl.DupWarned,'1/1/1900'),getdate()) > 5
)
begin
update mbl
set DupWarned = getdate()
from inserted i
inner join tblMiscBillingLog mbl
on i.MiscBillingLog = mbl.MiscBillingLog
and i.VendorsID = mbl.VendorsID
and i.sngUnitsConsumed = mbl.sngUnitsConsumed
and i.curRelativeCost = mbl.curRelativeCost
raiserror('Duplicate entry warning: this invoice may already exist. This entry is not yet saved. Check the values and retry.', 16, 1)
end
else
begin
insert into tblMiscBillingLog (MiscBillingLog, JobsID, sngUnitsConsumed, VendorsID, curRelativeCost)
select MiscBillingLog, JobsID, sngUnitsConsumed, VendorsID, curRelativeCost
from inserted
end
go
--run the inserts separately:
-- clean insert
insert into tblMiscBillingLog (MiscBillingLog, JobsID, sngUnitsConsumed, VendorsID, curRelativeCost)
values ('test1',190672,1,11959,330.00);
--clean insert, different invoice, same job
insert into tblMiscBillingLog (MiscBillingLog, JobsID, sngUnitsConsumed, VendorsID, curRelativeCost)
values ('test2',190672,1,11959,330.00);
--should cause warning message and not be inserted even though it's a different job
insert into tblMiscBillingLog (MiscBillingLog, JobsID, sngUnitsConsumed, VendorsID, curRelativeCost)
values ('test1',190673,1,11959,330.00);
--should not cause message and be inserted if run within 5 minutes of last insert ;)
insert into tblMiscBillingLog (MiscBillingLog, JobsID, sngUnitsConsumed, VendorsID, curRelativeCost)
values ('test1',190673,1,11959,330.00);
select * from tblMiscBillingLog
--clean up
drop table tblMiscBillingLog;
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
June 10, 2011 at 11:03 am
Todd,
Messing with proprietary apps like this is probably asking for more trouble than it's worth. I've had to do it and I've found it's usually easier and more sound to build a little side app that does what you want.
I would go ahead and let the app do what it wants and log the fact of the duplicate in the trigger. Another little side app could review the duplicates in the log and either let them be or delete them. This concept, however, could also be messy if these are orders waiting to be fulfilled. The duplicate could get shipped before the review was done.
Todd Fifield
June 16, 2011 at 9:47 am
tfifield (6/10/2011)
Todd,Messing with proprietary apps like this is probably asking for more trouble than it's worth. I've had to do it and I've found it's usually easier and more sound to build a little side app that does what you want.
What is funny about you saying this is that I used to work for the software vendor developing and supporting this product. The vendor let me go and the customer hired me two days later to support the same product. So even though it is proprietary, I know the ins and outs of the software better than anyone at the vendor. 😉
I would go ahead and let the app do what it wants and log the fact of the duplicate in the trigger. Another little side app could review the duplicates in the log and either let them be or delete them. This concept, however, could also be messy if these are orders waiting to be fulfilled. The duplicate could get shipped before the review was done.
Todd Fifield
This is a good idea, but #1 I still have to create a new trigger to log this info and #2 someone would have to review the new system for duplicates which that person doesn't know the paperwork as well as the original user.
Thank you all for your expert opinions, I evaluate and consider them all. I have this trigger working exactly like I want it to on the test server and I don't see any adverse effects. I'll be putting it into production on the next update cycle and good or bad I'll let you all know how it turns out. :w00t:
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
June 22, 2011 at 12:55 pm
So here is my update, as promised, unabridged. I put the additional column and new trigger into production yesterday...and a part I didn't test before, the generate billing screen, returned a failed message. :w00t: :crying: But the bug is not in the trigger as listed above. I got lazy and changed the INSTEAD OF INSERT trigger to an INSTEAD OF INSERT, UPDATE on a whim. Unexplainable amateur moment. :pinch: Of course, if only the definition like that is changed, the ELSE part will not update but INSERT a brand new record. Good thing the validation triggers stopped the new insert.
I removed the UPDATE part and everything has been running as expected. I have to rework the trigger a bit to also check for edits, but at least I know to do it properly with testing 😀
Moral of the story: if you have a form bound to the table, you can add some addition error checking and escape the insert in a trigger. Also, don't wing it on CM day. 😎
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply