SophiaPretty

(5)

$14/per page/Negotiable

About SophiaPretty

Levels Tought:
Elementary,Middle School,High School,College,University,PHD

Expertise:
Accounting,Algebra See all
Accounting,Algebra,Applied Sciences,Architecture and Design,Art & Design,Biology,Business & Finance,Calculus,Chemistry,Communications,Computer Science,Economics,Engineering,English,Environmental science,Essay writing Hide all
Teaching Since: Jul 2017
Last Sign in: 305 Weeks Ago, 1 Day Ago
Questions Answered: 15833
Tutorials Posted: 15827

Education

  • MBA,PHD, Juris Doctor
    Strayer,Devery,Harvard University
    Mar-1995 - Mar-2002

Experience

  • Manager Planning
    WalMart
    Mar-2001 - Feb-2009

Category > Computer Science Posted 16 Dec 2017 My Price 10.00

T-SQL script in the given space beneath each problem

SQL based questions.

Please submit answers that actually run based on the database which I will include.

  • /*
        Assignment 14 (Chapter 15 Triggers, 10 points for Q1 and 30 points for Q2)
    */

    --Total points obtained:      (leave blank for TA's grading)

    --Your name:

    --Write one batch of T-SQL script in the given space beneath each problem
    --description to solve the problem.

    --Partial credits is possible.

    --Do not include statements other than the one for grading.

    --Do not change the file name and its extension.

    use [ClearWater]
    go


    --> Execute the following two statements to have a fresh copy of
    --  Products_copy for this assignment.

    if object_id('Products_copy') is not null
        drop table Products_copy;

    select * into Products_copy from Products;
    go

    /* Q1. (10 points)
    A product in the Products_copy may or may not have a unit price.
    Create an AFTER trigger called 'tgr_priceValidation' to ensure
    that, if a product has a unit price, it must not be at least $0.50
    but no higher than $500.00. Any attempt that violates this rule
    will be aborted by this trigger and receive an error 51234 with
    a message "No product is allowed to have a unit price out of the
    range between $0.50 and $500.00."

    Note that any action query, i.e., INSERT, DELETE, and UPDATE, can
    affect more than one row of data. So, if multiple products are
    affected by an action query, your trigger must be able to examine
    all of them in order to satisfy the above business rule.

    Several test cases are provided for your testing. If toyr trigger
    is implemented correctly, you should get the passed/failed results
    as shown in each test case.
    */
    go





    go

    --Test Case 1: passed

    insert into Products_copy ([ProductID], [ProductName], [UnitPrice], [Discontinued])
      values (301, 'Product 301', NULL, 1),
             (302, 'Product 302', 199.5, 1);

    --Test Case 2: failed

    insert into Products_copy ([ProductID], [ProductName], [UnitPrice], [Discontinued])
      values (303, 'Product 301', 22, 1),
             (304, 'Product 302', 0.25, 1);

    --Test Case 3: passed

    update Products_copy
        set [UnitPrice] = NULL
        where [ProductID] <= 3;

    update Products_copy
        set [UnitPrice] = 0.5
        where [ProductID] <= 3;

    --Test Case 4: failed

    update Products_copy
        set [UnitPrice] = [UnitPrice] - 30
        where [ProductID] <= 15;

    update Products_copy
        set [UnitPrice] = -2.99
        where [ProductID] <= 10;


    /* Q2. (30 points)
    Execute the two statements given on top of Q1 to re-build the Products_copy
    and create an INSTEAD OF trigger called 'tgr_priceValidation_2' to perform
    exactly the same function as tgr_priceValidation does.

    You may use the same test cases provided in Q1 to test this new trigger.

    Hint: you must implement the firing operations in this trigger to replace
    (or recover) them because they are already rolled back by this INSTEAD OF
    trigger.
    */
    go





    go
    ---------------END----------------

Answers

SophiaPretty
(5)
Status NEW Posted 16 Dec 2017 12:12 PM My Price 10.00

-----------  ----------- H-----------ell-----------o S-----------ir/-----------Mad-----------am ----------- Th-----------ank----------- yo-----------u f-----------or -----------you-----------r i-----------nte-----------res-----------t a-----------nd -----------buy-----------ing----------- my----------- po-----------ste-----------d s-----------olu-----------tio-----------n. -----------Ple-----------ase----------- pi-----------ng -----------me -----------on -----------cha-----------t I----------- am----------- on-----------lin-----------e o-----------r i-----------nbo-----------x m-----------e a----------- me-----------ssa-----------ge -----------I w-----------ill----------- be----------- qu-----------ick-----------ly

Not Rated(0)