Write PL/pgSQL using trigger for insertion of first_name, last_name, amount and payment_id into Customer_backup table whendeletion happens from Customer table considering below schema.Customer_backup (first_name, last_name, amount, payment_id)Customer (customer_id, payment_id, first_name, last_name, amount)Create tables with Primary Key, foreign key constraints in given schemas
Question
Write PL/pgSQL using trigger for insertion of first_name, last_name, amount and payment_id into Customer_backup table whendeletion happens from Customer table considering below schema.Customer_backup (first_name, last_name, amount, payment_id)Customer (customer_id, payment_id, first_name, last_name, amount)Create tables with Primary Key, foreign key constraints in given schemas
Solution
Sure, here is the PL/pgSQL code for creating the tables and the trigger:
First, let's create the Customer table:
CREATE TABLE Customer (
customer_id SERIAL PRIMARY KEY,
payment_id INT,
first_name VARCHAR(255),
last_name VARCHAR(255),
amount DECIMAL(10, 2)
);
Next, let's create the Customer_backup table:
CREATE TABLE Customer_backup (
first_name VARCHAR(255),
last_name VARCHAR(255),
amount DECIMAL(10, 2),
payment_id INT REFERENCES Customer(payment_id)
);
Now, let's create the trigger function:
CREATE OR REPLACE FUNCTION backup_customer() RETURNS TRIGGER AS $$
BEGIN
INSERT INTO Customer_backup(first_name, last_name, amount, payment_id)
VALUES (OLD.first_name, OLD.last_name, OLD.amount, OLD.payment_id);
RETURN OLD;
END;
$$ LANGUAGE plpgsql;
Finally, let's create the trigger:
CREATE TRIGGER backup_customer_trigger
BEFORE DELETE ON Customer
FOR EACH ROW
EXECUTE FUNCTION backup_customer();
This trigger will automatically insert the first_name, last_name, amount, and payment_id of any record deleted from the Customer table into the Customer_backup table.
Similar Questions
Write a PL/pgSQL block using an explicit cursor that will transfer the record of account no, customer name and balance from the“account” table to the “branch_surat” table if the branch name is surat in the “account” table. Furthermore, delete the record fromthe “account” table whichever record transfers to the “branch_surat” table.Use the following tables:account (ano, customer_name, balance, branch_name)branch_surat (ano, customer_name, balance)Create tables with Primary Key, foreign key constraints in given schemas
A) We have following relation orders(order_id,customer_id,order_date,amount). Create table with Primary Key, otherrelevant constraints in given schema.1) Find out the number of orders for each customer by customer_id and show only customer_id with number of orders above 5.2) Find out the total amount by order_id and order_date.B) Find the sum of a user-inserted number's first and last digits using PL/pgSQL function
Create PL/pgSQL procedure for the increment of employees where in salary less than 35000 will get hike of 15% in their previoussalary and other will get 10% hike in their previous salary. Using following schema, Employees (id, name, department, salary)call the procedure by id and print employee’s name with their updated salary
Life Plan Database: Create a trigger that after each customer was inserted the BILL table also updates.
reate PL/pgSQL procedure for the increment of employees where in salary less than 35000 will get hike of 15% in their previoussalary and other will get 10% hike in their previous salary. Using following schema, Employees (id, name, department, salary)call the procedure by id and print employee’s name with their updated salary
Upgrade your grade with Knowee
Get personalized homework help. Review tough concepts in more detail, or go deeper into your topic by exploring other relevant questions.