Skip to main content

Creating triggers

Triggers are SQL statements which are stored with the intention of being activated or fired when an event associated with a database table occurs. This could be any event including an INSERT, UPDATE and DELETE. Lets begin by creating a few simple insert triggers

  1. CREATE a trigger on the ORDERLINE table for the insert event. The trigger will update the corresponding PRODUCT record QTY_ORDERED by adding the value of QTY to QTY_ORDERED.
  2. CREATE TRIGGER tr_qty_ordered_value_insert 
    ON Orderline FOR INSERT
    AS
    BEGIN 
         UPDATE product 
          SET QTY_ORDERED =  QTY_ORDERED +
     ((SELECT qty from INSERTED) * (SELECT unitprice from INSERTED)) 
           WHERE product.ProductID = (SELECT ProductID from INSERTED);      
    END;
    Command(s) completed successfully.
    
  3. CREATE a trigger on the ORDERLINE table for the delete event. The trigger will update the corresponding PRODUCT record QTY_ORDERED by subtracting the value of QTY FROM QTY_ORDERED.
  4. CREATE TRIGGER tr_qty_ordered_value_delete 
    ON Orderline FOR DELETE
    AS
    BEGIN 
         UPDATE product 
          SET product.QTY_ORDERED =  product.QTY_ORDERED -
     ((SELECT OrderLine.qty from orderline) * 
      (SELECT OrderLine.unitprice from orderline)) 
           WHERE product.ProductID = (SELECT ProductID from orderline);          
    END;
    
    Command(s) completed successfully.
    
  5. CREATE a trigger on the ORDERLINE table for the update event. The trigger will update the corresponding PRODUCT record QTY_ORDERED by subtracting the OLD QTY and then adding the NEW QTY
  6. CREATE TRIGGER tr_qty_ordered_value_update
    ON Orderline FOR UPDATE
    AS
    BEGIN 
         UPDATE product
         SET QTY_ORDERED =  QTY_ORDERED -
          ((SELECT qty from DELETED) * (SELECT unitprice FROM DELETED))
                +
          ((SELECT qty FROM INSERTED) * (SELECT unitprice FROM INSERTED))
          WHERE product.ProductID = (SELECT ProductID from INSERTED);
    END;
    
     Command(s) completed successfully.
    

Comments

Popular posts from this blog

:nth-child structural pseudo-class selectors

There are 4 pseudo-class expressions that are part of the :nth-child pseudo-class. Structural pseudo-class selectors target HTML elements based on the DOM tree. Basically, elements that cannot easily be targeted by simple selectors or combinations of selectors. What makes pseudo-classes so handy is the ability style elements dynamically based on its position in the DOM. :nth-of-type(N) :nth-last-child(N) :nth-child(N) :nth-last-of-type(N) :nth-of-type(N) selector My favorite of the 4 is the :nth-of-type(N) selector. The nth-of-type selector allows you to select child elements of a parent based on the particular type of the element, for example every 5th "li" element in a list. You can select even or odd elements, or the nth (order number) child in the group of elements. The class accepts the argument "n" which can can be a keyword, a number, or strings "odd", "even", or an expression "-n+3". Let's look at a simple but ef

The ICMP protocol

Let's look into the ICMP protocol. Specifically, ping and traceroute. ICMP is the Internet Control Message Protocol and is a component of the IP Layer. Basically, used by hosts to communicate diagnostic network layer information that is carried in the IP payload. It communicates error messages which are acted on by the IP layer or the UDP or TCP protocols. All of the exercises were carried out using the open source network protocol analyzer "Wireshark". www.wireshark.org Describe in detail the protocols ARP and ICMP. ARP is the Address Resolution Protocol is similar to that of DNS. Where DNS resolves IP addresses to domain names, ARP resolves network layer IP addresses to link layer MAC addresses. In order to send a datagram the source must give the adaptor the IP address and the MAC address. For example, host A wants to send a packet to host B. Host A uses a cached ARP table to look up the IP address for any existing records of host B's MAC address. If the MA