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.

Conditional FK Deletes

How would I use a Foreign Key to prevent deletions on the parent table?

For example, I have an Orders table and an OrderCancels table related by a FK on the iOrdID. When the app requests to delete a record on the Orders table, I need to check the OrderCancels table via the iOrdID FK for corresponding records, and if there are, not delete the order.I am a little confused as the foreign key prevents exactly that - deleting a parent record that has a child. If you application attempts to delete a parent record that has child records enforced using a foreign key constraint then the application will receive an error from sql server which will tell it that the delete was unsuccessful.|||Let me clarify - I know you can check 'Cascade deletes' in the SQL GUI so that if you delete a parent, the child gets deleted as well. This is how all of our current FKs work.

Are you saying that if I want to check for children and cancel the parental deletion if the children are found, all I have to do is uncheck 'Cascade Deletes' on the FK? If so, how do I capture this error and return it to the app.

Sorry if all this seems obvious, but I am a very green DBA...

TIA,

-Justin|||You never mentioned cascading - this is very important.

The answer is yes. SQL Server automatically sends this error to the calling application.

Take a look at the books online "Cascading Referential Integrity Constraints" article.