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>;

Question

Write the code for the following triggers in MySQL, 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.

Answer 1

Question

Chapter 8, Problem 9TD

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

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...

Expert Solution & Answer

Trending nowThis is a popular solution!

See solution

Check out sample textbook solution

Students have asked these similar questions

Write the code for the following triggers in MySQL, 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.

THIS MODULE IS ABOUT SUBQUERIES, SO YOU MUST USE SUBQUERIES INSTEAD OF TABLE JOINS (a) From which two fields in which table can you calculate the enrollment of a section?(b) From which table can you get both course and section numbers?(c) Which field in table SECTION uniquely identifies a section, section id or section no?(d) Write a SQL statement to show sections and their enrollment.(e) Write a SQL statement to show sections with enrollment greater than five. Display both course andsection numbers

T-SQL procedure A: obtain the name and credit limit of the customer whose number currently is stored in I_CUSTOMER_NUM. Place these valuse in the variables I_CUSTOMER_NAME and I_CREDIT_LIMIT, respectively. Output the content of I_CUSTOMER_NAME and I_CREDIT_LIMIT. B: obtain the order date, customer number and name for the order whose number currently is stored in I_ORDER_NUM. place these values in the variables I_ORDER_DATE, I_CUSTOMER_NUM and I_CUSTOMER_NAME , respectively. output the contents of I_ORDER_DATE, I_CUSTOMER_NUM , and I_CUSTOMER_NAME. C: add a row to the ORDERS table. D: change the date of the whole number is stored in I_ORDER_DATE to the date currently found in I_ORDER_DATE. E: delete the order whose number is stored in I_ORDER_NUM.

Answer 2

Question

Chapter 8, Problem 9TD

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

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...

Expert Solution & Answer

Trending nowThis is a popular solution!

See solution

Check out sample textbook solution

Answer 3

Question

Chapter 8, Problem 9TD

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

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...

Expert Solution & Answer

Trending nowThis is a popular solution!

See solution

Check out sample textbook solution

Answer 4

Question

Chapter 8, Problem 9TD

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

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...

Expert Solution & Answer

Trending nowThis is a popular solution!

See solution

Check out sample textbook solution

Answer 5

Chapter 8, Problem 9TD

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

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...

Answer 6

Chapter 8, Problem 9TD

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>;

Answer 7

Chapter 8, Problem 9TD

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>;

Answer 8

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...

Answer 9

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...

Answer 10

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...

Answer 11

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...

Answer 12

Expert Solution & Answer

Answer 13

Expert Solution & Answer

Answer 14

Trending nowThis is a popular solution!

See solution

Check out sample textbook solution

Answer 15

Trending nowThis is a popular solution!

See solution

Check out sample textbook solution

Answer 16

Trending nowThis is a popular solution!

See solution

Check out sample textbook solution

Answer 17

Trending nowThis is a popular solution!

Answer 18

Trending nowThis is a popular solution!

Answer 19

Trending nowThis is a popular solution!

Answer 20

Trending now

Answer 21

This is a popular solution!

Answer 22

See solution

Check out sample textbook solution

Answer 23

Ch. 8 - Prob. 1RQ Ch. 8 - Prob. 2RQ Ch. 8 - Prob. 3RQ Ch. 8 - Prob. 4RQ Ch. 8 - Prob. 5RQ Ch. 8 - Prob. 6RQ Ch. 8 - Prob. 7RQ Ch. 8 - Prob. 8RQ Ch. 8 - Prob. 9RQ Ch. 8 - In PL/SQL, how do you assign variables the same...

Solution Summary: The author explains the syntax to create a trigger, which is executed at the time of data modification or meets some data description.

Explanation of Solution

Explanation of Solution

Chapter 8 Solutions