Oracle PL/SQL Assignment 9-9: Tracking Pledge Payment Activity The DoGood Donor

Question

Oracle PL/SQL Assignment 9-9: Tracking Pledge Payment Activity

The DoGood Donor organization wants to track all pledge payment activity. Each time a pledge
payment is added, changed, or removed, the following information should be captured in a
separate table: username (logon), current date, action taken (INSERT, UPDATE, or DELETE),
and the idpay value for the payment record. Create a table named DD_PAYTRACK to hold
this information. Include a primary key column to be populated by a sequence, and create a
new sequence named DD_PTRACK_SEQ for the primary key column. Create a single trigger for
recording the requested information to track pledge payment activity, and test the trigger.

Explanation / Answer

CREATE SEQUENCE DD_PTRACK_SEQ
MINVALUE 1
MAXVALUE 99999
START WITH 1
INCREMENT BY 1
CACHE 20;

CREATE TABLE DD_PAYTRACK (
id        NUMBER(10)    NOT NULL,
username VARCHAR2(100) NOT NULL,
curr_date DATE default SYSDATETIME(),
action   VARCHAR2(10) NOT NULL

CONSTRAINT books_pk PRIMARY KEY (id)

);

CREATE OR REPLACE TRIGGER PAYTRACK
BEFORE DELETE OR INSERT OR UPDATE ON DD_PAYTRACK
FOR EACH ROW
DECLARE
   v_ChangeType VARCHAR(10);

BEGIN
  
   IF INSERTING THEN
     v_ChangeType := 'INSERT';
   ELSIF UPDATING THEN
     v_ChangeType := 'UPDATE';
   ELSE
     v_ChangeType := 'DELETE';
   END IF;

   INSERT INTO DD_PAYTRACK
   VALUES (DD_PTRACK_SEQ.NEXTVAL,USER,SYSDATE,v_ChangeType);;
END
/

Related Questions

Navigate

• Browse (All)
• Browse O
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.