Professional Web Applications Themes

TRIGGER not called when it should - Microsoft SQL / MS SQL Server

You can still get rows for which fNeedsA = 0 AND fNeedsB = 0 is true if you insert them with those values. If you really want to make sure that those rows never appear in your table, don't rely on an external app, but put that logic in a trigger: CREATE TRIGGER DoNotInsertWhenBothZero ON T FOR INSERT AS BEGIN IF ROWCOUNT = 0 RETURN INSERT INTO T (fNeedsA, fNeedsB ) SELECT fNeedsA, fNeedsB from inserted WHERE fNeedsA <> 0 AND fNeedsB <> 0 END -- Jacco Schalkwijk MCDBA, MCSD, MCSE Database Administrator Eurostop Ltd. "Zig Mandel" <z_man_del[remove underscores and ...

  1. #1

    Default Re: TRIGGER not called when it should

    You can still get rows for which fNeedsA = 0 AND fNeedsB = 0 is true if you
    insert them with those values. If you really want to make sure that those
    rows never appear in your table, don't rely on an external app, but put that
    logic in a trigger:

    CREATE TRIGGER DoNotInsertWhenBothZero
    ON T
    FOR INSERT
    AS
    BEGIN
    IF ROWCOUNT = 0 RETURN
    INSERT INTO T (fNeedsA, fNeedsB )
    SELECT fNeedsA, fNeedsB
    from inserted
    WHERE fNeedsA <> 0 AND fNeedsB <> 0
    END

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Zig Mandel" <z_man_del[remove underscores and this]mac.com> wrote in
    message news:phx.gbl... 
    that 
    case.) 
    fNeedsB 
    works 


    Jacco Guest

  2. #2

    Default Re: TRIGGER not called when it should

    Maybe the trigger is being disabled at some point? (ALTER TABLE T DISABLE
    TRIGGER DeleteWhenBothZero)

    BULK INSERT can also bypass triggers.

    I would suggest that a table constraint is a better alternative to your
    triggers:
    .... CHECK (NOT (fneedsa=0 AND fneedsb=0))

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --

    "Zig Mandel" <z_man_del[remove underscores and this]mac.com> wrote in
    message news:phx.gbl... 
    that 
    case.) 
    fNeedsB 
    works 


    David Guest

Similar Threads

  1. Replies: 8
    Last Post: December 19th, 03:08 PM
  2. Replies: 0
    Last Post: November 29th, 10:21 AM
  3. Replies: 0
    Last Post: November 29th, 10:03 AM
  4. XML.TRIGGER
    By Dan-C in forum Macromedia Flash Data Integration
    Replies: 1
    Last Post: February 28th, 03:55 PM
  5. Trigger
    By James in forum ASP Database
    Replies: 1
    Last Post: August 6th, 01:05 AM

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