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;

Related questions

Concept explainers

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