Professional Web Applications Themes

Trigger Help - Microsoft SQL / MS SQL Server

I have a trigger (code below). Where do I put in IF statements to kick off the CASE? I put in a commented out example of the IF I want to do in the code. Any help would be appreciated! ALTER Trigger dbo.CreditLimit_tg on membership_detail FOR insert, update as Declare Level2 varchar(10), Ship_Customer varchar(8), ord varchar(10), Credit_Status varchar(6), Credit_Limit decimal(20,2) --Declare IND varchar(3) declare cr_limit cursor local for Select Level2 , order_no, Credit_Status, Credit_Limit from inserted i, Customer c open cr_limit fetch cr_limit into Level2 , ord, Credit_Status, Credit_Limit while (fetch_status = 0) If level2 = 'STAFF' and Credit_Limit <= ...

  1. #1

    Default Trigger Help

    I have a trigger (code below). Where do I put in IF
    statements to kick off the CASE? I put in a commented out
    example of the IF I want to do in the code. Any help
    would be appreciated!

    ALTER Trigger dbo.CreditLimit_tg
    on membership_detail
    FOR insert, update
    as

    Declare Level2 varchar(10),
    Ship_Customer varchar(8),
    ord varchar(10),
    Credit_Status varchar(6),
    Credit_Limit decimal(20,2)

    --Declare IND varchar(3)

    declare cr_limit cursor local for
    Select Level2 , order_no, Credit_Status, Credit_Limit
    from inserted i, Customer c

    open cr_limit

    fetch cr_limit into Level2 , ord, Credit_Status,
    Credit_Limit

    while (fetch_status = 0)
    If level2 = 'STAFF' and Credit_Limit <= '1500' and
    Credit_Status = 'OK'
    begin

    print 'Selecting Ship Customer'
    select ship_customer = ship_customer
    from membership (nolock)
    where order_no = ord

    print 'Ship Customer is '+ship_customer

    --If level2 = 'STAFF' and Credit_Limit <= '1500' and
    Credit_Status = 'OK'

    update c

    set c.credit_limit =
    case level2
    when 'STAFF' then 1500
    when 'Indv' then 1500
    When 'Org' then 2000
    when 'jr' then 1500
    when 'stu' then 100
    when 'sust' then 4000
    else 0

    end,
    c.credit_terms =
    'Net 30',

    modoper = substring(user,1,9)+'-00'
    from customer c
    Where Customer = ship_customer

    End

    close cr_limit
    deallocate cr_limit
    Mike Guest

  2. #2

    Default Re: Trigger Help

    I am not sure if you need a cursor here to get it done. Simply use a WHERE
    clause to filter out the rows & use a single update like:

    UPDATE customer
    SET credit_limit =
    CASE inserted.Level2
    WHEN 'STAFF' THEN 1500
    WHEN 'Indv' THEN 1500
    WHEN 'Org' THEN 2000
    WHEN 'jr' THEN 1500
    WHEN 'stu' THEN 100
    WHEN 'sust' THEN 4000
    ELSE 0
    END,
    credit_terms = 'Net 30',
    modoper = SUBSTRING(user, 1, 9) + '-00'
    FROM customer
    INNER JOIN membership (NOLOCK)
    ON customer.ship_customer = membership.ship_customer
    INNER JOIN inserted
    ON .... --- use the keycolumns in your join
    WHERE .... --- add the required filters

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Sen Guest

  3. #3

    Default Re: Trigger Help


    Thank you for your reply.

    There would be several if's though. One for each category.

    If level2 = 'STAFF' and Credit_Limit <= '1500' and Credit_Status =
    'OK'
    Then Case
    Else
    If level2 = 'STU' and Credit_Limit <= '100' and Credit_Status = 'OK'
    Else

    Etc...........


    *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    Don't just participate in USENET...get rewarded for it!
    Mike hendricks Guest

  4. #4

    Default Re: Trigger Help

    You can still use CASE expressions with multiple WHEN clauses or even nest
    the CASE expressions like :

    CASE WHEN inserted.Level2 = 'STAFF'
    AND inserted.Credit_Limit <= 1500
    AND inserted.Credit_Status = 'OK'
    THEN CASE ...

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Sen Guest

Similar Threads

  1. database trigger
    By cfpaperboy in forum Coldfusion Database Access
    Replies: 1
    Last Post: September 2nd, 09:26 PM
  2. XML.TRIGGER
    By Dan-C in forum Macromedia Flash Data Integration
    Replies: 1
    Last Post: February 28th, 03:55 PM
  3. Trigger
    By James in forum ASP Database
    Replies: 1
    Last Post: August 6th, 01:05 AM
  4. emailing from trigger
    By Andrea Worley in forum Microsoft SQL / MS SQL Server
    Replies: 4
    Last Post: July 9th, 06:07 PM
  5. Trigger key
    By Elisabeth Smith in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: July 2nd, 09:22 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