Problem with OR inside a trigger

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Problem with OR inside a trigger

Author

Topic

bxw689
Starting Member

4 Posts

Posted - 2007-12-09 : 19:47:39

I created the following trigger to ensure that Mastercard or Visa are entered as the credit card type is a credit card table. The trigger works fine when I use one type, but when I add or, it doesn't work. Any advice? Thanks

/* THIS TRIGGERS VERIFIES THAT A VALID CREDIT CARD TYPE IS ENTERED*/

CREATE TRIGGER trg_accepted_credit_card
on credit_card_payments
for insert
as
begin

declare @card_type varchar(50)

select @card_type = (select card_type from inserted)

if exists(select 1 from inserted where @card_type != 'Mastercard' OR @card_type != 'Visa')

begin
print 'Invalid Credit Card Type.'
rollback transaction
end
end

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-12-09 : 19:48:48

it should be a AND condition

alternatively you can use


where @card_type not in ( 'Mastercard' , 'Visa' )

KH
[spoiler]Time is always against us[/spoiler]

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-12-09 : 20:00:51

this trigger is broken in the case of inserts of multiple rows because of this line:

select @card_type = (select card_type from inserted)

what will be the value of @card_type if someone is inserting 1000 rows as a single transaction, all with different card types? and why store this in a variable at all? if you insist on a trigger, what you should have is this:


if exists(select 1 from inserted where card_type not in ('Mastercard','Visa'))
begin
  print 'Invalid Credit Card Type.'
  rollback transaction
end

however none of this is really necessary. this type of thing would be better implemented as a check constraint:


ALTER TABLE credit_card_payments
ADD CONSTRAINT allowed_types_of_credit_cards 
CHECK (card_type in ('Mastercard','Visa'))

elsasoft.org

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources