Conditional Trigger?

Still new to programming in SQL but need to do the following:

On a table with Provider information need to set a trigger? that will prevent anyone from using our own taxID number. The problem is that six Providers belong to us and for them that taxID is acceptable.

My question is can a constraint be set that applies to every row in the table except my list of six, if not how would I set up a trigger that will fire for INSERT and UPDATE that will exclude the six providerID's?

tblProvider is table
ProviderID is Unique Key
TaxID is field name for the tax id
Application only updates or inserts one row at a time

Thanks,

BrentIf I understand you right, try this... I assumed some information for this example... this constraint will let the TaxID be anything for ProviderIDs between 1 and 6 and restrict it from using TaxID for any other ProviderID

ALTER TABLE tblProvider
ADD CONSTRAINT ck_Provider_TaxID
CHECK (TaxID <> 100 or ProviderID in (1,2,3,4,5,6))

Originally posted by baolive
Still new to programming in SQL but need to do the following:

On a table with Provider information need to set a trigger? that will prevent anyone from using our own taxID number. The problem is that six Providers belong to us and for them that taxID is acceptable.

My question is can a constraint be set that applies to every row in the table except my list of six, if not how would I set up a trigger that will fire for INSERT and UPDATE that will exclude the six providerID's?

tblProvider is table
ProviderID is Unique Key
TaxID is field name for the tax id
Application only updates or inserts one row at a time

Thanks,

Brent|||Thanks for the reponse HueyStLoui.

Tried the code and it seems to be having a problem with the TaxID column. As it verifies against the first entry it gives the following error:

Syntax error converting the varchar value '111-11-1111' to a column of data type int.

(NOTE: the number has been changed as I don't want to post someones SSN).

The TaxID column is a varchar15. Data contained is either in SSN format (NNN_NN_NNNN) or Federal Tax ID (NN-NNNNNNN).

Brent|||Brent,

You will need to change the constraint I posted slightly to match the datatypes of your columns (I just assumed both where integers). If TaxID is a varchar, just put single quotes around the value... (same goes for ProviderID if it is a varchar as well). Note.. you will need to change the ProviderID's listed to be the 6 you mentioned in your original post.

ALTER TABLE tblProvider
ADD CONSTRAINT ck_Provider_TaxID
CHECK (TaxID <> '111-11-1111' or ProviderID in (1,2,3,4,5,6))

Originally posted by baolive
Thanks for the reponse HueyStLoui.

Tried the code and it seems to be having a problem with the TaxID column. As it verifies against the first entry it gives the following error:

Syntax error converting the varchar value '111-11-1111' to a column of data type int.

(NOTE: the number has been changed as I don't want to post someones SSN).

The TaxID column is a varchar15. Data contained is either in SSN format (NNN_NN_NNNN) or Federal Tax ID (NN-NNNNNNN).

Brent|||Thanks,
Looks like that will work for us.