1- create a trigger which records all changes (insert, update, delete) made on t
ID: 3703069 • Letter: 1
Question
1- create a trigger which records all changes (insert, update, delete) made on the table account in a new table called account_history () , this is the table coulmn - Account: account_id, cust_id,amount,creation_date , 2- create procedure AccountSelectCommand : which has as input num and returns an account number num . idea for this procedure that we input the acount id and it return amount . this is the tables and coulmn : Account: account_id, cust_id,amount,creation_date sql devlober the langauage is pl/sql ...
Explanation / Answer
Q1) create a trigger which records all changes (insert, update, delete) made on the table account in a new table called account_history () , this is the table coulmn - Account: account_id, cust_id,amount,creation_date
ON ACCOUNT
ACCOUNT_ID ACCOUNT.ACCOUNTID%TYPE;
ACCOUNT_CUST_ID ACCOUNT.CUST_ID%TYPE;
ACCOUNT_AMOUNT ACCOUNT.AMOUNT%TYPE;
ACCOUNT_CREATION_DATE ACCOUNT.CREATION_DATE%TYPE;
IF INSERTING THEN
ACCOUNT_ID := :NEW.ACCOUNT_ID;
ACCOUNT_CUST_ID := :NEW.CUST_ID;
ACCOUNT_CREATION_DATE := :NEW.CREATION_DATE;
ACCOUNT_AMOUNT := :NEW.AMOUNT;
ELSIF UPDATING THEN
ACCOUNT_ID := :NEW.ACCOUNT_ID;
ACCOUNT_CUST_ID := :NEW.CUST_ID;
ACCOUNT_CREATION_DATE := :NEW.CREATION_DATE;
ACCOUNT_AMOUNT := :NEW.AMOUNT;
ACCOUNT_ID := :OLD.ACCOUNT_ID;
ACCOUNT_CUST_ID := :OLD.CUST_ID;
ACCOUNT_CREATION_DATE := :OLD.CREATION_DATE;
ACCOUNT_AMOUNT := :OLD.AMOUNT;
END IF;
INSERT INTO account_history VALUES (ACCOUNT_ID , ACCOUNT_CUST_ID , ACCOUNT_AMOUNT , ACCOUNT_CREATION_DATE );
Q2) AccountSelectCommand : which has as input num and returns an account number num
CREATE OR REPLACE PROCEDURE AccountSelectCommand (ACCOUNT_NUM ACCOUNT.ACCOUNT_ID%TYPE)
IS
VAR_CUST_ID ACCOUNT.CUST_ID%TYPE;
VAR_AMOUNT ACCOUNT.AMOUNT%TYPE;
VAR_CREATION_DATE ACCOUNT.CREATION_DATE%TYPE;
BEGIN
SELECT CUST_ID, AMOUNT, CREATION_DATE INTO VAR_CUST_ID, VAR_AMOUNT, VAR_CREATION_DATE FROM ACCOUNT WHERE ACCOUNT_ID = ACCOUNT_NUM;
DBMS_OUTPUT.PUT_LINE('CUSTOMER ID: ' || VAR_CUST_ID);
DBMS_OUTPUT.PUT_LINE('AMOUNT: ' || VAR_AMOUNT);
DBMS_OUTPUT.PUT_LINE('CREATION DATE: ' || VAR_CREATION_DATE);
END;
/
Please let me know in case of any clarifications required. Thanks!
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.