SQL script stops running after trigger introduced <p>I am designing a fictional database for a bank and I am required to write trigger that keeps tabs of the total loan amount that each bank-branch loans out. Here is my load file:</p> <pre><code>CREATE TABLE a2_bank ( routing_code VARCHAR(200) PRIMARY KEY, name VARCHAR(200) NOT NULL, address VARCHAR(200) NOT NULL ); INSERT INTO a2_bank VALUES ( '123456','ASB', '3 gladstone rd'); INSERT INTO a2_bank VALUES ( '123556','BNZ', '5 gladstone rd'); INSERT INTO a2_bank VALUES ( '12456','KIWIBANK', '3 gladstone rd'); CREATE TABLE a2_bankbranch ( name VARCHAR(200) NOT NULL, branch_num VARCHAR(200) PRIMARY KEY, address VARCHAR(200) NOT NULL, routing_code VARCHAR(200) NOT NULL, total_loan NUMBER(38), FOREIGN KEY(routing_code) REFERENCES a2_bank(routing_code) ); INSERT INTO a2_bankbranch VALUES ( 'ASB', '5', '3 gladstone rd', '123456', ''); INSERT INTO a2_bankbranch VALUES ( 'ASB', '4', '28 stevee rd', '123456', ''); CREATE TABLE a2_loan ( loan_num CHAR(10) PRIMARY KEY, type VARCHAR(200) NOT NULL, amount NUMBER(38) NOT NULL, contract_date DATE NOT NULL, branch_num VARCHAR(200) NOT NULL, routing_code VARCHAR(200) NOT NULL, FOREIGN KEY(routing_code) REFERENCES a2_bank(routing_code), FOREIGN KEY(branch_num) REFERENCES a2_bankbranch(branch_num) ); @trig.sql INSERT INTO a2_loan VALUES ( '323', 'Mortgage', '2000000', TO_DATE('11-03-1994', 'DD-MM-YYYY'), '5', '123456' ); INSERT INTO a2_loan VALUES ( '33', 'Car', '2000', TO_DATE('12-08-1994', 'DD-MM-YYYY'), '5', '123456' ); INSERT INTO a2_loan VALUES ( '3243', 'Pesonal', '875', TO_DATE('14-06-1994', 'DD-MM-YYYY'), '5', '123456' ); INSERT INTO a2_loan VALUES ( '6', 'Mortgage', '400500', TO_DATE('11-06-1994', 'DD-MM-YYYY'), '5', '123456' ); CREATE TABLE a2_account ( acc_num CHAR(10) PRIMARY KEY, type VARCHAR(20) NOT NULL, balance VARCHAR(10) NOT NULL ); INSERT INTO a2_account VALUES ( '2539267332', 'Savings', '20'); INSERT INTO a2_account VALUES ( '8237893378', 'Cash', '300'); INSERT INTO a2_account VALUES ( '2378723936', 'Cheque', '75'); INSERT INTO a2_account VALUES ( '2378723937', 'Savings', '175'); CREATE TABLE a2_customer ( ird_num CHAR(8) PRIMARY KEY, name VARCHAR(200) NOT NULL, address VARCHAR(200) NOT NULL, phone VARCHAR(20) ); INSERT INTO a2_customer VALUES ( '25362672', 'Stan Yel', '5 Wanna way', '02010201'); INSERT INTO a2_customer VALUES ( '83783783', 'Cam Birch', '34 Trada st', '02302020202'); INSERT INTO a2_customer VALUES ( '23723367', 'Jeff King', '5 Queens st', '38982383'); INSERT INTO a2_customer VALUES ( '54637822', 'John Smith', '24 Queen st', '38922383'); CREATE TABLE a2_accr ( ird_num CHAR(8) NOT NULL , account_num CHAR(10) NOT NULL, FOREIGN KEY(ird_num) REFERENCES a2_customer(ird_num), FOREIGN KEY(account_num) REFERENCES a2_account(acc_num) ); INSERT INTO a2_accr VALUES ( '25362672', '2539267332'); INSERT INTO a2_accr VALUES ( '83783783', '8237893378'); INSERT INTO a2_accr VALUES ( '83783783', '2378723937'); CREATE TABLE a2_loanr ( ird_num CHAR(8) NOT NULL , loan_num CHAR(10) NOT NULL, FOREIGN KEY(ird_num) REFERENCES a2_customer(ird_num), FOREIGN KEY(loan_num) REFERENCES a2_loan(loan_num) ); INSERT INTO a2_loanr VALUES ( '54637822', '323'); INSERT INTO a2_loanr VALUES ( '23723367', '33'); COMMIT; </code></pre> <p>Here is my trigger in order to achieve the desired result:</p> <pre><code>-- Create a trigger that will update the total loan amount --that each bank brach may have CREATE OR REPLACE TRIGGER ttl AFTER INSERT, UPDATE, DELETE ON a2_loan FOR EACH ROW BEGIN IF INSERTING THEN UPDATE a2_bankbranch SET total_loan = nvl(total_loan,0) + :new.amount WHERE routing_code = :new.routing_code AND branch_num = :new.branch_num; END IF; IF UPDATING THEN UPDATE a2_bankbranch SET total_loan = nvl(total_loan,0) - :old.amount WHERE routing_code = :old.routing_code AND branch_num = :old.branch_num; UPDATE a2_bankbranch SET total_loan = nvl(total_loan,0) + :new.amount WHERE routing_code = :new.routing_code AND branch_num = :new.branch_num; END IF; IF DELETING THEN UPDATE a2_bankbranch SET total_loan = nvl(total_loan,0) - :old.amount WHERE routing_code = :old.routing_code AND branch_num = :old.branch_num; END IF; END; </code></pre> <p>Currently when I run my load.sql script in Oracle it gets down to where I am trying to introduce my trig.sql and it simply stops and comes up with the line that the trig.sql is on. Any advice?</p>