May 18, 2009 at 8:39 am
Hello, I am trying to write a simple insert trigger that will check if a row exists in a table based on the inserted values. If so do NOT do the insert. Here is what I am trying but it inserts the row even if it already exists:
CREATE TRIGGER [PAYMENT_INS] ON dbo.Payment
FOR INSERT
AS
BEGIN TRAN
BEGIN TRY
IF NOT EXISTS(SELECT * FROM payment p, inserted i WHERE p.ORD_ID = i.ORD_ID AND p.SEQ_NUM = i.SEQ_NUM)
BEGIN
INSERT dbo.payment
(ORD_ID, PAY_METHOD, PAY_AMT, PAY_AUTH, COMPLETE_FLG, PAY_NUM, EXPIRE_DT, PAY_INFO, SEQ_NUM)
SELECT DISTINCT
i.ORD_ID, i.PAY_METHOD, i.PAY_AMT, i.PAY_AUTH, i.COMPLETE_FLG, i.PAY_NUM, i.EXPIRE_DT, i.PAY_INFO, i.SEQ_NUM
FROM inserted i
END
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() ErrorNBR, ERROR_SEVERITY() Severity, ERROR_LINE() ErrorLine, ERROR_MESSAGE() Msg
ROLLBACK TRAN
END CATCH
Best Regards,
~David
May 18, 2009 at 9:06 am
David Kranes (5/18/2009)
Hello, I am trying to write a simple insert trigger that will check if a row exists in a table based on the inserted values. If so do NOT do the insert. Can you please provide a simple example. Thank you.
I guess my question would be why do you need to do this with a trigger? Any reason why this wouldn't be caught with your Primary Key constraint? If you're trying to eliminate multiple PK values for a list of values in the row, then it would seem you just need to either extend your PK as your PK is not living up to it's function as uniquely indentifying the row.
If that is not an option for you, check into an index with a Unique constraint.
-Luke.
May 18, 2009 at 9:11 am
would it work as you wanted it if it were an 'INSTEAD OF INSERT' trigger?
and I agree with Luke .. a Unique constraint is probably the way to go.
May 18, 2009 at 9:12 am
Seems you updated your post as I was posting...
My question still stands, but I also would like to bring up something you may or may not be thinking about... This seems to be for some sort of accounting system. It looks like you are only allowing 1 payment to be made per order... How are you going to handle Orders where multiple payments are made (i.e. installments) or multiple types of payments (i.e. spread over more than one Credit Card, or some cash, some credit etc.) How are you going to handle adjustments? i.e. My order is for $100 and I pay you $100, however I am a tax exempt organization and only after I made my payment I send you in the required forms for tax exemption. You will need to make an adjustment to the total cost of the order and perhaps send me a check for the difference.
Just a few things to think about as you are architecting your solution.
-Luke.
May 18, 2009 at 9:14 am
Not only that, but you are trying to insert records that have already been "inserted" into the table. I agree with Luke. You need to extend your primary key and drop the trigger, or place the criteria you are using in your trigger on the insert statement itself and drop the trigger.
May 18, 2009 at 9:15 am
The seq_num handles the multiple payments per transaction. This is newly added so we can not go back and change or remove existing data. That is why there can not be a pk on ord_id and seq_num.
Best Regards,
~David
May 18, 2009 at 9:25 am
At this point I have no choice but to use a trigger as bad a design as it is. Any idea why the NOT EXISTS clause is not working.
Best Regards,
~David
May 18, 2009 at 9:28 am
The problem with the trigger is that it doesn't tell the database what to do if there ARE matches, it just tells it what to do if there are not.
So, if there are any matches, it just goes ahead with the default insert.
That's why you're getting duplicates.
I agree that this should be handled in an insert proc, instead of a trigger. But, if it needs to be a trigger, it should be something like:
if object_id(N'dbo.Payment') is not null
drop table dbo.Payment
go
create table dbo.Payment (
ID int identity primary key,
Ord_ID int,
Seq_Num int);
go
create trigger Payment_Ins on dbo.Payment
instead of insert
as
if exists
(select *
from dbo.Payment p
inner join inserted i
on p.Ord_ID = i.Ord_ID
and p.Seq_Num = i.Seq_Num)
begin
rollback
end
else
begin
insert into dbo.Payment (Ord_ID, Seq_Num)
select distinct Ord_ID, Seq_Num
from inserted
end
go
insert into dbo.Payment (Ord_ID, Seq_Num)
select 1, 1 union all
select 1, 1 union all
select 2, 1;
go
insert into dbo.Payment (Ord_ID, Seq_Num)
select 1, 1 union all
select 3, 1;
go
select *
from dbo.Payment;
(Of course, this is a test harness. Don't run the drop command, etc., in your production database.[/i])
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 18, 2009 at 9:31 am
Lynn Pettis (5/18/2009)
Not only that, but you are trying to insert records that have already been "inserted" into the table.
Which is why I'm suggesting an INSTEAD OF trigger. You still get the inserted temp table, so your existing trigger should work if you changed its type.
Or am I missing something really obvious?
May 18, 2009 at 9:32 am
Another option:
if object_id(N'dbo.Payment') is not null
drop table dbo.Payment
go
create table dbo.Payment (
ID int identity primary key,
Ord_ID int,
Seq_Num int);
go
create trigger Payment_Ins on dbo.Payment
instead of insert
as
insert into dbo.Payment (Ord_ID, Seq_Num)
select distinct Ord_ID, Seq_Num
from inserted
except
select Ord_ID, Seq_Num
from dbo.Payment
go
insert into dbo.Payment (Ord_ID, Seq_Num)
select 1, 1 union all
select 1, 1 union all
select 2, 1;
go
insert into dbo.Payment (Ord_ID, Seq_Num)
select 1, 1 union all
select 3, 1;
go
select *
from dbo.Payment;
This one, the trigger won't insert duplicate rows, and will instead insert only those that aren't matched. That means you can have a partial insert, instead of a rollback. The prior one is more ACID, but the second one might be an option, depending on your business needs.
Again, DON'T RUN THIS IN YOUR PRODUCTION DATABASE, it's a test script only. You'll need to decide which, if either, to use, and then modify just the trigger creation to match your needs.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 18, 2009 at 9:49 am
Since an INSTEAD OF INSERT trigger fires prior to the actual INSERT, I'd actually vote for Gus's solution, but I also agree with him on the testing in a development environment first.
May 18, 2009 at 10:10 am
GSquared, both your examples were very helpful. Thank you all for your time and efforts.
Best Regards,
~David
May 18, 2009 at 1:29 pm
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply