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...
Trending nowThis is a popular solution!
Chapter 8 Solutions
A Guide to SQL
- A Guide to SQLComputer ScienceISBN:9781111527273Author:Philip J. PrattPublisher:Course Technology PtrDatabase Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781285196145Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos CoronelPublisher:Cengage Learning
- Database Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781305627482Author:Carlos Coronel, Steven MorrisPublisher:Cengage Learning