Professional Web Applications Themes

Triggers - How do I ... - Microsoft SQL / MS SQL Server

Hello, I have just written my first trigger and I have few questions. The trigger is to adjust quantities after an order has been made. 1. "from Inserted" - this is reading the new inserted record buffer? 2. "from deleted" - this is reading the buffer for the one record that has just been deleted? 3. "Set InvtID = (Select InvtID from Inserted)" and then "Update Inventory Set QtySold = QtySold + 1, QtyonHand = QtyonHand - 1 Where Inventory.InvtID = InvtID" 4. Am I on the right track. 5. Is there a nice simple how to page on triggers ...

  1. #1

    Default Triggers - How do I ...

    Hello,
    I have just written my first trigger and I have few
    questions. The trigger is to adjust quantities after an
    order has been made.
    1. "from Inserted" - this is reading the new inserted
    record buffer?
    2. "from deleted" - this is reading the buffer for the one
    record that has just been deleted?
    3. "Set InvtID = (Select InvtID from Inserted)"
    and then
    "Update Inventory
    Set QtySold = QtySold + 1, QtyonHand = QtyonHand - 1
    Where Inventory.InvtID = InvtID"
    4. Am I on the right track.
    5. Is there a nice simple how to page on triggers some
    where?

    Charles
    Charles Guest

  2. #2

    Default Re: Triggers - How do I ...

    Hello,
    Yes my trigger is only for one record update at a time.
    so is this ok?

    CREATE TRIGGER tOrderDetailPulled ON [dbo].[OrderDetail]
    FOR UPDATE
    AS
    DECLARE Pulled char(1), InvtID int
    IF Update(Pulled)
    BEGIN
    IF (ROWCOUNT = 0 OR ROWCOUNT > 1) RETURN
    Set InvtID = (Select InvtID from Inserted)
    IF (Select Pulled from Inserted ) = 'Y'
    BEGIN
    Update Inventory
    Set QtySold = QtySold + 1, QtyonHand = QtyonHand - 1
    Where Inventory.InvtID = InvtID
    END
    IF (Select Pulled from Inserted) = ''
    BEGIN
    Update Inventory
    Set QtySold = QtySold - 1, QtyonHand = QtyonHand + 1
    Where Inventory.InvtID = InvtID
    And QtySold > 0
    END
    END

    Thanks for you help,
    Charles

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

  3. #3

    Default Re: Triggers - How do I ...

    Hi Charles,

    From your post I understand when [dbo].[OrderDetail] is updated, the
    trigger will fire. It checks if the update is for "Pulled" column. If it
    is, the trigger logic will be executed. Then it checks if only one row is
    updated. If no row or more than one row is updated, the trigger will not do
    anything. If updated "Pulled" column value is "Y", then it executes
    Update Inventory
    Set QtySold = QtySold + 1, QtyonHand = QtyonHand - 1
    Where Inventory.InvtID = InvtID
    If "Pulled" column value is "", it executes
    Update Inventory
    Set QtySold = QtySold - 1, QtyonHand = QtyonHand + 1
    Where Inventory.InvtID = InvtID
    And QtySold > 0

    This posting is provided "AS IS" with no warranties, and confers no rights.

    Sincerely,
    William Wang
    Microsoft Partner Online Support

    --------------------
    | From: Charles Wildner <net>
    | References: <012c01c35b78$14bc1720$gbl>
    | X-Newsreader: AspNNTP 1.50 (ActionJackson.com)
    | Subject: Re: Triggers - How do I ...
    | Mime-Version: 1.0
    | Content-Type: text/plain; cht="us-ascii"
    | Content-Transfer-Encoding: 7bit
    | Message-ID: <phx.gbl>
    | Newsgroups: microsoft.public.sqlserver.programming
    | Date: Tue, 05 Aug 2003 11:55:45 -0700
    | NNTP-Posting-Host: actionjackson133.dsl.frii.net 216.17.147.133
    | Lines: 1
    | Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTN GP10.phx.gbl
    | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.programming:379299
    | X-Tomcat-NG: microsoft.public.sqlserver.programming
    |
    | Hello,
    | Yes my trigger is only for one record update at a time.
    | so is this ok?
    |
    | CREATE TRIGGER tOrderDetailPulled ON [dbo].[OrderDetail]
    | FOR UPDATE
    | AS
    | DECLARE Pulled char(1), InvtID int
    | IF Update(Pulled)
    | BEGIN
    | IF (ROWCOUNT = 0 OR ROWCOUNT > 1) RETURN
    | Set InvtID = (Select InvtID from Inserted)
    | IF (Select Pulled from Inserted ) = 'Y'
    | BEGIN
    | Update Inventory
    | Set QtySold = QtySold + 1, QtyonHand = QtyonHand - 1
    | Where Inventory.InvtID = InvtID
    | END
    | IF (Select Pulled from Inserted) = ''
    | BEGIN
    | Update Inventory
    | Set QtySold = QtySold - 1, QtyonHand = QtyonHand + 1
    | Where Inventory.InvtID = InvtID
    | And QtySold > 0
    | END
    | END
    |
    | Thanks for you help,
    | Charles
    |
    | *** Sent via Developersdex http://www.developersdex.com ***
    | Don't just participate in USENET...get rewarded for it!
    |

    William Guest

  4. #4

    Default Re: Triggers - How do I ...

    Hello William,

    A few questions.

    1. if "from Inserted" is the way to reference the updated buffer how do
    I reference the before update buffer? ie. if I need to compare something
    before and after.

    2. is "Set InvtID=(Select InvtID from Inserted)" the best way to get
    the Inventory Key from the Order and then update the Inventory Items
    quantity fields? or is there a better way?

    3. my qty is not correct, I need to add something like
    "Set Qty=(Select Qty from Inserted)" and then us Qty as the amount
    to update the totals. is there a better way?


    Charles




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

  5. #5

    Default Re: Triggers - How do I ...

    Hi Charles,

    1. You can use INSTEAD OF trigger to retrieve before and after image.

    2. You may want to use Select InvtID= InvtID from Inserted.

    3. That's OK, you may use Qty as the amount to update the totals.

    This posting is provided "AS IS" with no warranties, and confers no rights.

    Sincerely,

    William Wang

    Microsoft Partner Online Support

    --------------------
    | From: Charles Wildner <net>
    | References: <phx.gbl>
    | X-Newsreader: AspNNTP 1.50 (ActionJackson.com)
    | Subject: Re: Triggers - How do I ...
    | Mime-Version: 1.0
    | Content-Type: text/plain; cht="us-ascii"
    | Content-Transfer-Encoding: 7bit
    | Message-ID: <phx.gbl>
    | Newsgroups: microsoft.public.sqlserver.programming
    | Date: Wed, 06 Aug 2003 07:54:37 -0700
    | NNTP-Posting-Host: actionjackson133.dsl.frii.net 216.17.147.133
    | Lines: 1
    | Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftn gp13.phx.gbl
    | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.programming:379485
    | X-Tomcat-NG: microsoft.public.sqlserver.programming
    |
    | Hello William,
    |
    | A few questions.
    |
    | 1. if "from Inserted" is the way to reference the updated buffer how do
    | I reference the before update buffer? ie. if I need to compare something
    | before and after.
    |
    | 2. is "Set InvtID=(Select InvtID from Inserted)" the best way to get
    | the Inventory Key from the Order and then update the Inventory Items
    | quantity fields? or is there a better way?
    |
    | 3. my qty is not correct, I need to add something like
    | "Set Qty=(Select Qty from Inserted)" and then us Qty as the amount
    | to update the totals. is there a better way?
    |
    |
    | Charles
    |
    |
    |
    |
    | *** Sent via Developersdex http://www.developersdex.com ***
    | Don't just participate in USENET...get rewarded for it!
    |

    William Guest

Similar Threads

  1. Triggers, again.. ;-)
    By Net in forum PostgreSQL / PGSQL
    Replies: 7
    Last Post: February 23rd, 02:45 AM
  2. triggers in DB2
    By Piotr in forum IBM DB2
    Replies: 2
    Last Post: August 27th, 03:37 PM
  3. Triggers
    By P. Saint-Jacques in forum IBM DB2
    Replies: 0
    Last Post: August 8th, 02:30 AM
  4. Help with triggers
    By W Gemini in forum IBM DB2
    Replies: 0
    Last Post: August 7th, 11:45 AM
  5. Triggers and Users
    By Vishal in forum Microsoft SQL / MS SQL Server
    Replies: 4
    Last Post: July 14th, 09:12 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