Using the following simple relations person(id,name,address,city,state,zip) pers
ID: 3548475 • Letter: U
Question
Using the following simple relations
person(id,name,address,city,state,zip)
person_changes(timestamp, id, old_name, old_address, old_city, old_state, old_zip)
1. Write a stored procedure that takes the id, name, address, city, state and zip as parameters. This procedure should copy the information that exists in the person table for the id to the person changes table BEFORE updating the person table with the new information passed in to the procedure. I will leave it to you to read the postgres documentation to find the function to get the current time which should be used to provide the value for timestamp.
2. Implement the same functionality but use a trigger instead.
Explanation / Answer
1)
create or replace procedure person_proc(v_id person.id%type,v_name person.name%type,v_address person.address%type,v_city person.city%type,v_state person.state%type,v_zip person.zip%type) is
begin
insert into person_changes(timestamp, id, old_name, old_address, old_city, old_state, old_zip) values
select sysdate,id,name,address,city,state,zip from person where id = v_id
commit;
update person set name = v_name,address = v_address,city = v_city,state = v_state,zip = v_zip where id= v_id;
commit;
end person_proc;
2)
create or replace trigger person_trig
AFTER UPDATE ON person
FOR EACH ROW
BEGIN
insert into person_changes(timestamp, id, old_name, old_address, old_city, old_state, old_zip) values
(sysdate,:OLD.name,:OLD.id,:OLD.addrress,:OLD.city,:OLD.state,:OLD.zip);
commit;
end person_trig;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.