Create a trigger called membership_balance_updates that will capture any updates

Question

Create a trigger called membership_balance_updates that will capture any updates made to the balance column in membership. The trigger should only capture those transactions in which the member’s balance actually changes. The membership_id, old balance, new balance, user, transaction date should be placed into a membership_balance_audit table.

Use the following script to create the membership_balance_audit table.

CREATE TABLE IF NOT EXISTS membership_balance_audit

(mem_num                   INTEGER,

old_mem_balance     DECIMAL(10,2),

new_mem_balance DECIMAL(10,2),

transaction_date       TIMESTAMP,

transaction_user       VARCHAR(50));

Then do the following ?

b) Run the following update statements after the trigger has been created. UPDATE membership SET balance = 10.00 WHERE membership_id = 104; UPDATE membership SET first_name = 'Dan' WHERE membership_id = 103;

Explanation / Answer

create table TBL_MEMBERSHIP
(
membership_id VARCHAR2(50),
first_name VARCHAR2(50),
balance NUMBER
)
/

create table MEMBERSHIP_BALANCE_AUDIT
(
mem_num NUMBER,
old_mem_balance NUMBER(10,2),
new_mem_balance NUMBER(10,2),
transaction_date TIMESTAMP(6),
transaction_user VARCHAR2(50)
)
/

insert into tbl_membership (MEMBERSHIP_ID, FIRST_NAME, BALANCE)
values ('103', 'Dan', 10);

insert into tbl_membership (MEMBERSHIP_ID, FIRST_NAME, BALANCE)
values ('104', 'kaja', 10);
/
commit;
/

/
CREATE OR REPLACE TRIGGER membership_balance_updates
AFTER UPDATE
ON tbl_membership
FOR EACH ROW

DECLARE
v_username varchar2(10);

BEGIN

-- Find username of tbl_membership performing UPDATE into table
SELECT user INTO v_username
FROM dual;

-- Insert record into audit table
INSERT INTO membership_balance_audit
( mem_num,
old_mem_balance,
new_mem_balance,
transaction_user )
VALUES
( :new.membership_id,
:old.balance,
:new.balance,
v_username );

END;

/

insert into membership_balance_audit (MEM_NUM, OLD_MEM_BALANCE, NEW_MEM_BALANCE, TRANSACTION_DATE, TRANSACTION_USER)
values (104, 10.00, 20.00, null, 'SYSTEM');

insert into membership_balance_audit (MEM_NUM, OLD_MEM_BALANCE, NEW_MEM_BALANCE, TRANSACTION_DATE, TRANSACTION_USER)
values (103, 10.00, 50.00, null, 'SYSTEM');

After excuting two update statements these above two rows created in my table

Please check and comment

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

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