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