I am having an error and cannot figure out how to solve this question. Could someone please assist? Please be advised, I am using PostgreSQL as that is what was used in class. Thus, I must use PLPGSQL
Hello,
I am having an error and cannot figure out how to solve this question. Could someone please assist?
Please be advised, I am using PostgreSQL as that is what was used in class. Thus, I must use PLPGSQL
Application: PostgreSQL 12 or 13
PLPGSQL
CREATE OR REPLACE FUNCTION Moreno_03_bankTriggerFunction()
RETURNS TRIGGER
LANGUAGE PLPGSQL
AS
$BODY$
CREATE TRIGGER Moreno_03_bankTrigger
AFTER DELETE ON account
FOR EACH ROW
EXECUTE PROCEDURE Moreno_15_bankTriggerFunction();
TABLES (if needed)
CREATE TABLE branch (
branch_name varchar(35),
branch_city varchar(15),
assets numeric(15,2) CHECK (assets > 0.00),
CONSTRAINT branch_pkey PRIMARY KEY (branch_name)
);
CREATE TABLE customer (
ID varchar(15),
customer_name varchar(25) NOT NULL,
customer_street varchar(35),
customer_city varchar(15),
CONSTRAINT customer_pkey PRIMARY KEY (ID)
);
CREATE TABLE loan (
loan_number varchar(15),
branch_name varchar(35),
amount numeric(15,2),
CONSTRAINT loan_pkey PRIMARY KEY (loan_number),
CONSTRAINT loan_fkey FOREIGN KEY (branch_name) REFERENCES branch (branch_name)
ON DELETE SET NULL
);
CREATE TABLE borrower (
ID varchar(15),
loan_number varchar(15),
CONSTRAINT borrower_pkey PRIMARY KEY (ID, loan_number),
CONSTRAINT borrower_fkey_1 FOREIGN KEY (ID) REFERENCES customer (ID)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT borrower_fkey_2 FOREIGN KEY (loan_number) REFERENCES loan (loan_number)
ON DELETE CASCADE
ON UPDATE CASCADE
);
CREATE TABLE account (
account_number varchar(25),
branch_name varchar(35) NOT NULL,
balance numeric(15,2) DEFAULT 0.00,
CONSTRAINT account_pkey PRIMARY KEY (account_number),
CONSTRAINT account_fkey FOREIGN KEY (branch_name) REFERENCES branch (branch_name)
ON DELETE SET NULL
);
CREATE TABLE depositor (
ID varchar(15),
account_number varchar(15),
CONSTRAINT depositor_pkey PRIMARY KEY (ID, account_number),
CONSTRAINT depositor_fkey FOREIGN KEY (ID) REFERENCES customer (ID)
ON DELETE CASCADE
ON UPDATE CASCADE
-- No fkey constrains on account number as it is managed via trigger.
);
Trending now
This is a popular solution!
Step by step
Solved in 2 steps
