Professional Web Applications Themes

SQL Server Trigger Question - Microsoft SQL / MS SQL Server

Tryin' to do something relatively simple but I'm just drawing a blank. Before an INSERT takes place, I want to check another table. The other table must have the primary key already existing AND one other field must be NOT NULL. If either of these fails, I want to stop the INSERT. I know how to make a constraint if we were dealing with just the primary key. But adding the extra criteria for the other field is escaping me. How do I get an INSERT not to happen if the conditions fails?...

  1. #1

    Default SQL Server Trigger Question

    Tryin' to do something relatively simple but I'm just drawing a blank.

    Before an INSERT takes place, I want to check another table. The
    other table must have the primary key already existing AND one other
    field must be NOT NULL. If either of these fails, I want to stop the
    INSERT.

    I know how to make a constraint if we were dealing with just the
    primary key. But adding the extra criteria for the other field is
    escaping me.

    How do I get an INSERT not to happen if the conditions fails?
    tommy Guest

  2. #2

    Default Re: SQL Server Trigger Question

    Tommy,

    You can have a foreign key constraint that has multiple columns (compound),
    but that combination must be a unique or primary key, that you can foreign key to.

    If you dont have this then it looks like this functionality will require a trigger.

    On SQL 2000 ( as against earlier versions ) you have two types of trigger.
    For/After and InsteadOf.

    With After triggers, as the name suggests the trigger fires after the row has
    already been inserted/deleted/updated and therefore any constraint checks passed
    - but you can issue a rollback to abort the update. So you could put the
    foreign key check in for the one field, and use the trigger to check for the not null other.

    With an instead of trigger you have to do the insert/update/delete yourself,
    so you have more control, and could do both checks in the trigger. It's how
    things were programmed in the old days where there were no Declaritive
    Referential Integrity.

    Regards
    AJ

    "tommy" <com> wrote in message news:google.com... 


    Andrew Guest

  3. #3

    Default Re: SQL Server Trigger Question

    Tommy
    look at these scripts
    SELECT A.TABLE_NAME, A.COLUMN_NAME
    FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE A JOIN
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS B
    ON A.CONSTRAINT_NAME = B.CONSTRAINT_NAME AND A.TABLE_NAME =
    B.TABLE_NAME
    WHERE B.CONSTRAINT_TYPE = 'PRIMARY KEY' AND
    OBJECTPROPERTY(OBJECT_ID
    (A.TABLE_SCHEMA +
    N'.' +
    A.TABLE_NAME), 'IsMSShipped') = 0


    ------------------------
    SELECT TABLE_NAME,*
    FROM INFORMATION_SCHEMA.COLUMNS WHERE IS_NULLABLE='NO'

    Note: You need to modify these scrips for you needs WHERE condition
    table_name and check if all coluns are nonnullable
    I'd write stored procedure thta checks all these condition and then perfom
    inserts statement.
    You can write INSTEAD OF trigger but It will performance cost.




    "tommy" <com> wrote in message
    news:google.com... 


    Uri Guest

Similar Threads

  1. SQL Server Trigger question
    By ego-adam in forum Coldfusion Database Access
    Replies: 3
    Last Post: October 14th, 01:46 AM
  2. An trigger question
    By ppl in forum Informix
    Replies: 1
    Last Post: November 12th, 05:30 PM
  3. question about trigger
    By Piotr in forum IBM DB2
    Replies: 7
    Last Post: September 17th, 01:49 PM
  4. trigger question
    By Newbie in forum Microsoft SQL / MS SQL Server
    Replies: 25
    Last Post: August 26th, 06:13 AM
  5. Trigger question ...
    By Bob Castleman in forum Microsoft SQL / MS SQL Server
    Replies: 7
    Last Post: July 3rd, 04:22 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139