A: Create a trigger called Membership_balance_updates that will capture any upda
ID: 3759560 • Letter: A
Question
A: Create a trigger called Membership_balance_updates that will capture any updates made to the mem_balance colum in membership. The trigger should only capture those transactions in which the member's balance actually changes: the mem_number, old mem_balance, new balance, user transaction date should be placed into a membership_balance_audti 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)); Run the following update statements after the trigger has been created. UPDATE membership SET mem_balance = 10.00 WHERE mem_num = 104; UPDATE membership SET mem_fname = 'Dan' WHERE mem_num = 103; Write the SELECT statement to show all records in the membership_balance_audit table. (Note: The transaction date will be different for your result set
B): Run the following update statement after the trigger has been created
UPDATE membership SET mem_balance= 10.00 WHERE mem_Num = 104;
UPDATE membership SET mem_fname = 'Dane' WHERE mem_num= 103;
C): write select statement to show all the record in member_balance_audit table:
Explanation / Answer
CREATE OR REPLACE FUNCTION add_log_trigg_function()
RETURNS trigger AS
$BODY$
DECLARE
account_type varchar;
BEGIN
IF (TG_TABLE_NAME = 'account_current') THEN
account_type := 'Current';
RAISE NOTICE 'TRIGER called on %', TG_TABLE_NAME;
ELSIF (TG_TABLE_NAME = 'account_savings') THEN
account_type := 'Savings';
RAISE NOTICE 'TRIGER called on %', TG_TABLE_NAME;
END IF;
RETURN null;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION add_log_trigg_function()
OWNER TO postgres;
Now we got the account type of the customer by checking the TG_TABLE_NAME variable. Next we are gonna handle the INSERT operation.
CREATE OR REPLACE FUNCTION add_log_trigg_function()
RETURNS trigger AS
$BODY$
DECLARE
account_type varchar;
BEGIN
IF (TG_TABLE_NAME = 'account_current') THEN
account_type := 'Current';
RAISE NOTICE 'TRIGER called on %', TG_TABLE_NAME;
ELSIF (TG_TABLE_NAME = 'account_savings') THEN
account_type := 'Savings';
RAISE NOTICE 'TRIGER called on %', TG_TABLE_NAME;
END IF;
IF (TG_OP = 'INSERT') THEN
INSERT INTO log(
log_time,
description)
VALUES(
now(),
'New customer added. Account type: ' || account_type || ', Customer ID: ' || NEW.customer_id || ', Name: ' || NEW.customer_name || ', Balance: ' || NEW.balance);
RETURN NEW;
END IF;
RETURN null;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
Here we are checking the TG_OP variable to find whether an INSERT is operation is performed. If so it inserts a log entry in log table using a simple insert command. Notice that we are using the NEW variable to get the customer_id, name and balance of the new customer being inserted. The NEW variable is a special variable of type RECORD holding the new row to be inserted or updated. Think of it like a table with a single row of data. We can get the values of each column by NEW.column_name or even NEW.* for entire columns at once.
Also notice, if the IF condition is true then the function will return NEW. Otherwise it will return null, that means it will skip all further operations, as I said earlier. In the next step we are going to include the UPDATE and DELETE operations:
CREATE OR REPLACE FUNCTION add_log_trigg_function()
RETURNS trigger AS
$BODY$
DECLARE
account_type varchar;
BEGIN
IF (TG_TABLE_NAME = 'account_current') THEN
account_type := 'Current';
RAISE NOTICE 'TRIGER called on %', TG_TABLE_NAME;
ELSIF (TG_TABLE_NAME = 'account_savings') THEN
account_type := 'Savings';
RAISE NOTICE 'TRIGER called on %', TG_TABLE_NAME;
END IF;
IF (TG_OP = 'INSERT') THEN
INSERT INTO log(
log_time,
description)
VALUES(
now(),
'New customer added. Account type: ' || account_type || ', Customer ID: ' || NEW.customer_id || ', Name: ' || NEW.customer_name || ', Balance: ' || NEW.balance);
RETURN NEW;
ELSIF (TG_OP = 'UPDATE') THEN
IF (NEW.balance < 0) THEN
RAISE EXCEPTION 'Can''t withdraw the amount because of low balance! Available balance: %, Requested amount: %', OLD.balance, OLD.balance + (- NEW.balance);
END IF;
IF NEW.balance != OLD.balance THEN
EXECUTE 'INSERT INTO log(log_time,description) VALUES(now(), ''Balance updated. Account type: ' || account_type || ', Customer ID: '' || $1.customer_id || ''. Old balance: '' || $2.balance || '', New balance: '' || $1.balance)' USING NEW, OLD;
END IF;
RETURN NEW;
ELSIF (TG_OP = 'DELETE') THEN
INSERT INTO log(
log_time,
description)
VALUES(
now(),
'Account deleted. Account type: ' || account_type || ', Customer ID: ' || OLD.customer_id);
RETURN OLD;
END IF;
RETURN null;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
Inside UPDATE’s IF condition we have two more IF conditions. First one to manage negative balance condition. If the NEW balance is found to be negative we will raise an Exception which will skip all further processes. In next IF condition we inserts the actual log entry. You can see here I have used dynamic command execution using EXECUTE command. Because some times you will need to use this technique. Be careful while adding quotes for literal values. Because here the query itself is written as a string and any further literal value inside the query needs secondary quoting with multiple single quotes(two single quotes, eg: ”value”). We are passing the NEW and OLD variables via the USING expression, so that $1 hold NEW and $2 holds OLD.
CREATE OR REPLACE FUNCTION add_log_trigg_function()
RETURNS trigger AS
$BODY$
DECLARE
account_type varchar;
BEGIN
IF (TG_TABLE_NAME = 'account_current') THEN
account_type := 'Current';
RAISE NOTICE 'TRIGER called on %', TG_TABLE_NAME;
ELSIF (TG_TABLE_NAME = 'account_savings') THEN
account_type := 'Savings';
RAISE NOTICE 'TRIGER called on %', TG_TABLE_NAME;
END IF;
RETURN null;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION add_log_trigg_function()
OWNER TO postgres;
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.