EXAMINE THE TRIGGER BELOW IS IT CORRECT? IF NOT MODIFY Create test to show that the triggers are correctly implemented, do the following: Truncate the Transaction table Reset the Tx_Nbr sequence back to 1 Update the Account table, setting the Balance back to zero Re-run the INSERT statements for the transactions Use simple queries to demonstrate that the results in the Transaction and Account tables are as expected GIVEN THE TRIGGER BELOW: Trigger to enforce the referential integrity for the Transaction Ref_Nbr: Deposit or Withdrawal transaction to Bank Branch Bill Payment, Debit Purchase, or Return transaction to Merchant CREATE TRIGGER Transaction_RefNbr_Check BEFORE INSERT OR UPDATE ON Transactions FOR EACH ROW DECLARE TxRefNbr_count INTEGER; BEGIN IF:NEW.TxTypeCode = ‘D’ OR :NEW.TxTypeCode = ‘W’ THEN SELECT COUNT()INTO TxRefNbr_count FROM BRANCH WHERE BranchNbr=:NEW.RefNbr; IF TxRefNbr_count = 0 THEN RAISE_APPLICATION_ERROR(-20000, ‘Invalid Branch Number’); END IF; ELSIF: NEW.TxTypeCode=‘B’ OR :NEW.TxTypeCode=‘P’ OR :NEW.TxTypeCode=‘R’THEN SELECT COUNT()INTO TxRefNbr_count FROM MERCHANT WHERE BranchNbr=:NEW.RefNbr; IF TxRefNbr_count = 0 THEN RAISE_APPLICATION_ERROR(-20000, ‘Invalid Merchant Number’); END IF; END IF; END; Trigger to update the Account balance for each new transaction entered (assume that a transaction will never be updated or deleted). CREATE OR REPLACE TRIGGER UpdateAccountBalance AFTER INSERT ON Transactions FOR EACH ROW BEGIN UPDATE Account SET Balance= Balance+:NEW.TxAmount WHERE AccountNbr=:NEW.AccountNbr; END;

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Related questions

Concept explainers

Question

EXAMINE THE TRIGGER BELOW IS IT CORRECT? IF NOT MODIFY

Create test to show that the triggers are correctly implemented, do the following:

Truncate the Transaction table
Reset the Tx_Nbr sequence back to 1
Update the Account table, setting the Balance back to zero
Re-run the INSERT statements for the transactions
Use simple queries to demonstrate that the results in the Transaction and Account tables are as expected

GIVEN THE TRIGGER BELOW:

Trigger to enforce the referential integrity for the Transaction Ref_Nbr:

Deposit or Withdrawal transaction to Bank Branch
Bill Payment, Debit Purchase, or Return transaction to Merchant

CREATE TRIGGER Transaction_RefNbr_Check

BEFORE INSERT OR UPDATE ON Transactions

FOR EACH ROW

DECLARE

TxRefNbr_count INTEGER;

BEGIN

IF:NEW.TxTypeCode = ‘D’ OR :NEW.TxTypeCode = ‘W’ THEN

SELECT COUNT(*)INTO TxRefNbr_count

FROM BRANCH

WHERE BranchNbr=:NEW.RefNbr;

IF TxRefNbr_count = 0 THEN

RAISE_APPLICATION_ERROR(-20000, ‘Invalid Branch Number’);

END IF;

ELSIF: NEW.TxTypeCode=‘B’ OR :NEW.TxTypeCode=‘P’ OR :NEW.TxTypeCode=‘R’THEN

SELECT COUNT(*)INTO TxRefNbr_count

FROM MERCHANT

WHERE BranchNbr=:NEW.RefNbr;

IF TxRefNbr_count = 0 THEN

RAISE_APPLICATION_ERROR(-20000, ‘Invalid Merchant Number’);

END IF;

END;

Trigger to update the Account balance for each new transaction entered (assume that a transaction will never be updated or deleted).

CREATE OR REPLACE TRIGGER UpdateAccountBalance

AFTER INSERT ON Transactions

FOR EACH ROW

BEGIN

UPDATE Account SET Balance= Balance+:NEW.TxAmount

WHERE AccountNbr=:NEW.AccountNbr;

END;

CLIENT
Client Nbr <pk>
First Name
Last Name
Street
City
Prov_State
Postal Code
Phone
Email
OWNS_CLIENT_FK
OWNS
Client Nbr <pk.fk1>
Account Nbr <pk.fk2>
TRANSACTION ACCOUNT FK
TRANSACTION
Ix Nbr
<pk>
Account Nbr <fk1>
Tx Type Code <fk2>
Tx Date
Tx Amount
Ref Nbr
BANK BRANCH
Branch Nbr <pk>
Branch Name
OWNS ACCOUNT_FK
TX_TYPE_LOOKUP_FK
MERCHANT
Merchant Nbr <pk>
Merchant Name
ACCOUNT
Account Nbr <pk>
Balance
TX_TYPE
Tx Type Code
Tx Type Descript
<pk>

Expert Solution

Step by step

Solved in 2 steps

SEE SOLUTION Check out a sample Q&A here

Knowledge Booster

Learn more about

Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.

Similar questions