Insert TRIGGER help needed

  • I have to enforce business rules that say certain values can only be used with certain other values.  So I create a Permissable Combination table, which lists all the valid combinations.  Then when an insert or update is made, the trigger checks the combination table to see if it is valid and throws an exception if it is not.

    THE PROBLEM IS:  Our db has ANSI_NULLS off, so if one of the values is null and null is a valid combination in some cases, then my trigger fails.

    Here's the trigger:

    CREATE TRIGGER trigIU_ORG_TYPE

    ON ORG_TYPE

    FOR INSERT, UPDATE

    AS

       BEGIN

            DECLARE @blab blah ....

    /*  see how many rows are being inserted   */

            select @numinserted = count(*) from inserted

    /* see how many inserted rows match the valid combinations  */

         SELECT @numrows = count(*)  FROM inserted i inner join

               DOM_ORG_TYPE_PC p on i.function_code = p.function_code

             AND i.cmd_code = p.cmd_code

    /* if any of the rows fail, kill the transaction  */

     if (@numinserted <> @numrows )

       BEGIN

         SELECT @errno  = 30012,

         @errmsg = 'Cannot insert/update ORG_TYPE: combination invalid'

         GOTO error

       END

      RETURN 

      error:

          RAISERROR @errno @errmsg

        ROLLBACK TRANSACTION

        END

     

    The DOM_ORG_TYPE_PC table tooks like this:

    PC_id       function_code cmd_code

    ----------- ------------- --------

    1           YES             CP   

    2           YES             CC   

    3           YES             HQ   

    4           NO              NULL

    The ORG_TYPE table looks like:

    CREATE TABLE [ORG_TYPE] (

     ...PK goes here...

     [function_code] char(3) NOT NULL ,

     [cmd_code] char(6) NULL

    ) ON [PRIMARY]

    So if I run the following:

    insert into org_type  values ( 'YES', 'CC')

    insert into org_type  values ( 'YES', 'HQ')

    Everything runs as I expect, codes are valid.  But try ...

    insert into org_type  values ('NO', null)

    and the trigger throws the exception.  I've tried ANSI_NULLS both off and on, but it doesn't seem to matter.

    Can anyone see what I'm doing wrong here??

    if I have to select the cmd_code from inserted to see if it is null, and then put "where cmd_code is null" in my join, then it will mess up if there are multiple row inserts.  I'm alittle confused about how to solve this.

    Thanks for any advice. - Muaddib

     

     

  • suggestion - why not use your pc_id in your org_type table instead of function_code and cmd_code ?!







    **ASCII stupid question, get a stupid ANSI !!!**

  • Killing 2 birds with the 1 stone, first off the NULL issue, then the looming performance issue by using inefficient and unnecessary counts:

    IF EXISTS (

      SELECT *

      FROM inserted as i

      WHERE NOT EXISTS (

        SELECT *

        FROM DOM_ORG_TYPE_PC As p

        WHERE (p.function_code = i.function_code Or 

                   (p.function_code Is Null And i.function_code Is Null)) 

        AND     (i.cmd_code = p.cmd_code Or

                   (i.cmd_code Is Null And p.cmd_code Is Null)) 

      )

    )

    BEGIN

         SELECT @errno  = 30012,

         @errmsg = 'Cannot insert/update ORG_TYPE: combination invalid'

         GOTO error

  • Thanks PW,

    That will work fine.  I didn't think about the OR and checking for NULL on both tables.  Of course it looks simple now that I see it.  Also, I thought the count(*) would be fast enough since count uses index, but your exist solution is much better.

     

    Sushila, I can't use the PC_id like it is a normal db, I'm working on an international defense project and some of the tables are not mine to change.  I have to add tables and relationships to validate their data, but I can't change schemas. 

    - Muaddib

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply