menu
bartleby
search
close search
Hit Return to see all results
close solutoin list

Write the code for the following triggers in PL/SQL or T-SQL following the style shown in the text. a. When adding a customer, add the customer’s balance multiplied by the sales rep’s commission rate to the commission for the corresponding sales rep. b. When updating a customer, add the difference between the new balance and the old balance multiplied by the sales rep’s commission rate to the commission for the corresponding sales rep. c. When deleting a customer, subtract the balance multiplied by the sales rep’s commission rate from the commission for the corresponding sales rep.

BuyFindarrow_forward

A Guide to SQL

9th Edition
Philip J. Pratt
Publisher: Course Technology Ptr
ISBN: 9781111527273
BuyFindarrow_forward

A Guide to SQL

9th Edition
Philip J. Pratt
Publisher: Course Technology Ptr
ISBN: 9781111527273
Chapter 8, Problem 9TD
Textbook Problem
119 views

Write the code for the following triggers in PL/SQL or T-SQL following the style shown in the text.

  1. a. When adding a customer, add the customer’s balance multiplied by the sales rep’s commission rate to the commission for the corresponding sales rep.
  2. b. When updating a customer, add the difference between the new balance and the old balance multiplied by the sales rep’s commission rate to the commission for the corresponding sales rep.
  3. c. When deleting a customer, subtract the balance multiplied by the sales rep’s commission rate from the commission for the corresponding sales rep.

Program Plan Intro

a.

Trigger:

The trigger is a named set of SQL statement that is executed at the time of data modification or meets some data description.

  • If the stated trigger condition meets up, then the mentioned action in the trigger has been performed.

There are three parts of trigger; they are:

Event:

  • It specifies the change to be made, such as an UPDATE or DELETE of a record.

Condition:

  • It specifies the condition to examine the records about to be affected. For each record, that meets the condition given in the trigger.

Action:

  • The action performed after the start of the trigger.

The syntax to create a trigger is as given below:

  CREATE TRIGGER trigger_name

  {BEFORE| AFTER|INSTEAD OF} {INSERT|DELETE|UPDATE} ON

  table_name

  [FOR EACH{ROW|STATEMENT}] [WHEN(condition)]

  <triggered SQL statement here>;

Explanation of Solution

Query to create trigger:

CREATE OR REPLACE TRIGGER ADD_CUSTOMER

AFTER INSERT ON CUSTOMER FOR EACH ROW

BEGIN

UPDATE REP

SET COMMISSION = COMMISSION +:NEW.BALANCE * RATE;

END;

Explanation:

  • The above query is used to create a trigger named “ADD_CUSTOMER” to update the “COMMISSION” content in the “REP” table whenever a record is inserted in the “CUSTOMER” table.

The Query to view the contents in “REP” table before inserting value in the table “CUSTOMER” is giv...

Explanation of Solution

b.

Query to create trigger:

CREATE OR REPLACE TRIGGER UPD_CUSTOMER

AFTER UPDATE ON CUSTOMER FOR EACH ROW

BEGIN

UPDATE REP

SET COMMISSION = COMMISSION + ((:NEW.BALANCE - :OLD.BALANCE)*RATE);

END;

Explanation:

  • The above query is used to create a trigger named “UPD_CUSTOMER” to update the “COMMISSION” contents in the “REP” table whenever a record is inserted in the “CUSTOMER” table...

Explanation of Solution

c.

Query to create trigger:

CREATE OR REPLACE TRIGGER DEL_CUSTOMER

AFTER DELETE ON CUSTOMER FOR EACH ROW

BEGIN

UPDATE REP

SET COMMISSION = COMMISSION - (:OLD.BALANCE * RATE);

END;

Explanation:

  • The above query is used to create a trigger named “DEL_CUSTOMER” to update the “COMMISSION” contents in the “REP” table whenever a record is delete in the “CUSTOMER” table...

Still sussing out bartleby?

Check out a sample textbook solution.

See a sample solution

The Solution to Your Study Problems

Bartleby provides explanations to thousands of textbook problems written by our experts, many with advanced degrees!

Get Started

Chapter 8 Solutions

A Guide to SQL
Show all chapter solutions
add
Ch. 8 - How do you place the results of a SELECT command...Ch. 8 - Can you use INSERT, UPDATE, or DELETE commands...Ch. 8 - How do you use a SELECT command that retrieves...Ch. 8 - Which PL/SQL command activates a cursor?Ch. 8 - Which PL/SQL command selects the next row in a...Ch. 8 - Which PL/SQL command deactivates a cursor?Ch. 8 - How do you use SQL commands in Access?Ch. 8 - How do you process a collection of rows retrieved...Ch. 8 - How do you move to the next record in a recordset...Ch. 8 - What are triggers? What purpose do they serve?Ch. 8 - What is the purpose of the INSERTED and DELETED...Ch. 8 - When you run the following SQL command, the result...Ch. 8 - Use the TAL Distributors database (see Figure 1-2...Ch. 8 - List the customer number and name for all...Ch. 8 - List the customer number, name, and balance for...Ch. 8 - TAL Distributors is running a promotion that is...Ch. 8 - Write PL/SQL or T-SQL procedures to accomplish the...Ch. 8 - Write PL/SQL or T-SQL procedures to retrieve and...Ch. 8 - Write Access functions to accomplish the following...Ch. 8 - Write a stored procedure in PL/SQL or T-SQL that...Ch. 8 - Write the code for the following triggers in...Ch. 8 - Oracle SQL includes several date and time...Ch. 8 - Use the Colonial Adventure Tours database (see...Ch. 8 - List the trip ID and trip name for all trips that...Ch. 8 - List the reservation ID, trip date, and price for...Ch. 8 - Write PL/SQL or T-SQL procedures to accomplish the...Ch. 8 - Write PL/SQL or T-SQL procedures to retrieve and...Ch. 8 - Write Access functions to accomplish the following...Ch. 8 - Write a stored procedure in PL/SQL or T-SQL that...Ch. 8 - Because the TRIP table contains several columns...Ch. 8 - Use the Solmaris Condominium Group database (see...Ch. 8 - List the owner number and last name for all owners...Ch. 8 - Solmaris is offering a monthly discount for owners...Ch. 8 - Write PL/SQL or T-SQL procedures to accomplish the...Ch. 8 - Write PL/SQL or T-SQL procedures to retrieve and...Ch. 8 - Write Access functions to accomplish the following...Ch. 8 - Write a stored procedure in PL/SQL or T-SQL that...Ch. 8 - SQL includes many numerical functions. Two of...

Additional Engineering Textbook Solutions

Find more solutions based on key concepts
Show solutions add
What are the potential costs of implementing a database system?

Database Systems: Design, Implementation, & Management

What would the divider setting be to scribe an arc with a 3/8" radius?

Precision Machining Technology (MindTap Course List)

True or False? The camshaft is always located in the engine block.

Automotive Technology: A Systems Approach (MindTap Course List)

As you already know, roofing materials keep water from penetrating into the roof structure. There is a wide ran...

Engineering Fundamentals: An Introduction to Engineering (MindTap Course List)

What is a mast-type service entrance?

EBK ELECTRICAL WIRING RESIDENTIAL

If your motherboard supports ECC DDR3 memory, can you substitute non-ECC DDR3 memory?

A+ Guide to Hardware (Standalone Book) (MindTap Course List)

List steps to add a computer or mobile device to a wireless Network.

Enhanced Discovering Computers 2017 (Shelly Cashman Series) (MindTap Course List)

What types of information should be covered in a respirator training program?

Welding: Principles and Applications (MindTap Course List)