BuyFindarrow_forward

Database Systems: Design, Implemen...

12th Edition
Carlos Coronel + 1 other
Publisher: Cengage Learning
ISBN: 9781305627482

Solutions

Chapter
Section
BuyFindarrow_forward

Database Systems: Design, Implemen...

12th Edition
Carlos Coronel + 1 other
Publisher: Cengage Learning
ISBN: 9781305627482
Chapter 8, Problem 29P
Textbook Problem
333 views

Create a trigger named trg_line_prod that automatically updates the quantity on hand for each product sold after a new LINE row is added.

Program Plan Intro

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 TRG_LINE_PROD

AFTER INSERT ON LINE

FOR EACH ROW

BEGIN

UPDATEPRODUCT

SETP_QOH = P_QOH - :NEW.LINE_UNITS

WHEREPRODUCT.P_CODE = :NEW.P_CODE;

END;

/

Explanation:

  • The above query is used to create a trigger named “TRG_LINE_PROD” that automatically updates the “P_QOH” for the product sold after inserting new recorded in the “LINE” 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

Database Systems: Design, Implementation, & Management
Show all chapter solutions
add
Ch. 8 - Suppose you have two tables: EMPLOYEE and...Ch. 8 - Given the employee information in Question 11,...Ch. 8 - Given the employee information in Question 11,...Ch. 8 - Given the employee information in Question 11,...Ch. 8 - Why does the order of the operands (tables) matter...Ch. 8 - What MS Access and SQL Server function should you...Ch. 8 - What Oracle function should you use to calculate...Ch. 8 - Suppose a PRODUCT table contains two attributes,...Ch. 8 - What string function should you use to list the...Ch. 8 - What is a sequence? Write its syntax.Ch. 8 - What is a trigger, and what is its purpose? Give...Ch. 8 - What is a stored procedure, and why is it...Ch. 8 - What is embedded SQL and how is it used?Ch. 8 - What is dynamic SQL, and how does it differ from...Ch. 8 - Create the tables. (Use the MS Access example...Ch. 8 - Insert the data into the tables you created in...Ch. 8 - Write the query that will generate a combined list...Ch. 8 - Write the query that will generate a combined list...Ch. 8 - Write the query that will show only the duplicate...Ch. 8 - Write the query that will generate only the...Ch. 8 - Write the query to show the invoice number, the...Ch. 8 - Write the query for all the invoices that will...Ch. 8 - Write the query that will write Oracle sequences...Ch. 8 - Modify the CUSTOMER table to include two new...Ch. 8 - Assuming that you completed Problem 10, write the...Ch. 8 - Assuming that the CUSTOMER table contains a...Ch. 8 - Write the query that lists the average age of your...Ch. 8 - Write the trigger to update the CUST_BALANCE in...Ch. 8 - Write a procedure to add a new customer to the...Ch. 8 - Write a procedure to add a new invoice record to...Ch. 8 - Write a trigger to update the customer balance...Ch. 8 - Write a procedure to delete an invoice, giving the...Ch. 8 - Write a query to display the products that have a...Ch. 8 - Write a query to display the current salary for...Ch. 8 - Write a query to display the starting salary for...Ch. 8 - Write a query to display the invoice number, line...Ch. 8 - Write a single SQL command to increase all price...Ch. 8 - Write a query to display the customer code, first...Ch. 8 - LargeCo is planning a new promotion in Alabama...Ch. 8 - One of the purchasing managers is interested in...Ch. 8 - The purchasing manager is still concerned about...Ch. 8 - Create a trigger named trg_line_total to write the...Ch. 8 - Create a trigger named trg_line_prod that...Ch. 8 - Create a stored procedure named prc_inv_amounts to...Ch. 8 - Create a procedure named prc_cus_balance_update...Ch. 8 - Modify the MODEL table to add the attribute and...Ch. 8 - Write the queries to update the MOD_WAIT_CHG...Ch. 8 - Modify the CHARTER table to add the attributes...Ch. 8 - Write the sequence of commands required to update...Ch. 8 - Write the sequence of commands required to update...Ch. 8 - Write the command required to update the...Ch. 8 - Write the command required to update the...Ch. 8 - Write the command required to update the...Ch. 8 - Modify the PILOT table to add the attribute shown...Ch. 8 - Create a trigger named trg_char_hours that...Ch. 8 - Create a trigger named trg_pic_hours that...Ch. 8 - Create a trigger named trg_cust_balance that...Ch. 8 - Write a query to display all rows in the PATRON...Ch. 8 - Write a query to display the patron ID, book...Ch. 8 - Write a query to display the patron ID, patron...Ch. 8 - Write a query to display the book number, title...Ch. 8 - Write a query to display the author last name,...Ch. 8 - Write a query to display the author ID, book...Ch. 8 - Write a query to display the author last name,...Ch. 8 - Write a query to display the patron ID, book...Ch. 8 - Write a query to display the patron ID, full name...Ch. 8 - Write a query to display the book number and the...Ch. 8 - Write a query to display the author ID, first and...Ch. 8 - Write a query to display the book number, title,...Ch. 8 - Write a query to display the book number, title,...Ch. 8 - Write a query to display the book number, title,...Ch. 8 - Write a query to display the author ID, author...Ch. 8 - Write a query to display the patron ID, first and...Ch. 8 - Write a query to display the average number of...Ch. 8 - Write a query to display the patron ID and the...Ch. 8 - Write a query to display the book number, title,...Ch. 8 - Write a query to display the author ID, first and...Ch. 8 - Write a query to display the book number, title,...Ch. 8 - Write a query to display the book number, title,...Ch. 8 - Write a query to display the lowest average cost...Ch. 8 - Alter the DETAILRENTAL table to include a derived...Ch. 8 - Update the DETAILRENTAL table to set the values in...Ch. 8 - Alter the VIDEO table to include an attribute...Ch. 8 - Update the VID_STATUS attribute of the VIDEO table...Ch. 8 - Alter the PRICE table to include an attribute...Ch. 8 - Update the PRICE table to place the values shown...Ch. 8 - Create a sequence named rent_num_seq to start with...

Additional Engineering Textbook Solutions

Find more solutions based on key concepts
Show solutions add
What does it mean if a bond is callable

Cornerstones of Financial Accounting

This is a sports-related assignment. First look up the dimensions, and then show the dimensions on a diagram. Y...

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

Define a use case and a use case diagram, and prepare a sample of each.

Systems Analysis and Design (Shelly Cashman Series) (MindTap Course List)

Convert P = 5.00 atm into Pa, bar, and psia.

Fundamentals of Chemical Engineering Thermodynamics (MindTap Course List)

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

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

A worm is a destructive program designed as a real program.

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

Why are some propane and natural gas tips made with a deep, recessed center?

Welding: Principles and Applications (MindTap Course List)