Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2002
    Location
    Mancos, CO
    Posts
    73

    Unanswered: 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,

    Brent

  2. #2
    Join Date
    Sep 2002
    Location
    Atlanta, GA USA
    Posts
    19

    Arrow Re: Conditional Trigger?

    If 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

  3. #3
    Join Date
    Sep 2002
    Location
    Mancos, CO
    Posts
    73
    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

  4. #4
    Join Date
    Sep 2002
    Location
    Atlanta, GA USA
    Posts
    19

    Arrow

    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

  5. #5
    Join Date
    Sep 2002
    Location
    Mancos, CO
    Posts
    73
    Thanks,
    Looks like that will work for us.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •