Notice: This page requires JavaScript to function properly.
Please enable JavaScript in your browser settings or update your browser.
Leer Challenge: Creating After Trigger | Some Additional Topics
Advanced Techniques in SQL

book
Challenge: Creating After Trigger

Taak

Swipe to start coding

Now we will create a trigger to execute after updating the balance column of the BankAccounts table.
When triggered, it will invoke the function after_update_balance(), which logs the account number and the new balance into the UserLogs table, providing a record of balance modifications. The key idea is that we don't have to manually fill in the logs table - it will be done automatically by the trigger.

Note

Pay attention that triggers created for UPDATE operations can be designed to work only for update of the particular column. We can use the following statement to achive this:
CREATE TRIGGER trigger_name AFTER UPDATE OF col_name ON table_name.

Your task is to:

  • Create the after_update_balance() function. It should return a trigger as its result and perform the necessary action, which is updating logs.
  • Create an AFTER UPDATE trigger on the bankaccounts table. This trigger should use the designed function by executing it on each row of the update statement.

Oplossing

-- Create the trigger function
-- This function logs the update action whenever the balance of a bank account is updated.
CREATE OR REPLACE FUNCTION after_update_balance() RETURNS TRIGGER AS $$
BEGIN
-- Log the update action
-- Inserts a record into the UserLogs table with the new balance information.
INSERT INTO UserLogs (account_number, action)
VALUES (NEW.account_number, CONCAT('Account balance updated. New balance: ', NEW.balance));
RETURN NEW; -- Returns the updated row.
END;
$$ LANGUAGE plpgsql;

-- Drop trigger if exists
DROP TRIGGER IF EXISTS after_update_balance_trigger ON BankAccounts;

-- Create the trigger
-- This trigger is set to execute after an update operation on the balance column of the BankAccounts table.
CREATE TRIGGER after_update_balance_trigger
AFTER UPDATE OF balance ON BankAccounts
FOR EACH ROW
EXECUTE PROCEDURE after_update_balance(); -- Calls the after_update_balance function.

-- Update bank account balance
-- Updates the balance of the bank account with account number 202 to 1000.
UPDATE BankAccounts
SET balance = 1000
WHERE account_number = 202;

-- Retrieve updated UserLogs
-- Retrieves the UserLogs for the bank account with account number 202 to observe the changes.
SELECT * FROM UserLogs
WHERE account_number = 202;

Was alles duidelijk?

Hoe kunnen we het verbeteren?

Bedankt voor je feedback!

Sectie 3. Hoofdstuk 2
single

single

-- Create the trigger function
-- This function logs the update action whenever the balance of a bank account is updated.
CREATE OR REPLACE FUNCTION after_update_balance() ___ AS $$
BEGIN
-- Log the update action
-- Inserts a record into the userLogs table with the new balance information.
INSERT INTO userLogs (accountnumber, action)
VALUES (NEW.accountnumber, CONCAT('Account balance updated. New balance: ', NEW.balance));
RETURN NEW; -- Returns the updated row.
END;
$$ LANGUAGE plpgsql;

-- Drop trigger if exists
DROP TRIGGER IF EXISTS after_update_balance_trigger ON BankAccounts;

-- Create the trigger
-- This trigger is set to execute after an update operation on the balance column of the bankaccounts table.
CREATE TRIGGER after_update_balance_trigger
___ OF balance ON bankaccounts
FOR EACH ROW
___ after_update_balance(); -- Calls the after_update_balance function.

-- Update bank account balance
-- Updates the balance of the bank account with account number 202 to 1000.
UPDATE bankaccounts
SET balance = 1000
WHERE accountnumber = 202;

-- Retrieve updated userlogs
-- Retrieves the userlogs for the bank account with account number 202 to observe the changes.
SELECT * FROM userlogs
WHERE accountnumber = 202;
Query ResultQuery Result
No query executed yet...

Vraag AI

expand

Vraag AI

ChatGPT

Vraag wat u wilt of probeer een van de voorgestelde vragen om onze chat te starten.

some-alt