Automatically Updating Timestamps with PL/SQL Triggers

PL/SQL Trigger: Exercise-1 with Solution

Write a code in PL/SQL TO create a trigger that automatically updates a 'last_modified' timestamp whenever a row in a specific table is updated.

Sample Solution:

PL/SQL Code:

-- Create the 'employee' table
CREATE TABLE employee (
employee_id NUMBER PRIMARY KEY,
first_nameVARCHAR2(50),
last_nameVARCHAR2(50),
last_modified TIMESTAMP
);

-- Create a trigger to update the 'last_modified' timestamp
CREATE OR REPLACE TRIGGER update_last_modified
BEFORE UPDATE ON employee
FOR EACH ROW
BEGIN
:NEW.last_modified := SYSTIMESTAMP;
END;
/

Let's see how the trigger functions:

PL/SQL Code:

-- Insert a sample record
INSERT INTO employee (employee_id, first_name, last_name) VALUES (1, 'John', 'Doe');

-- Verify the initial timestamp
SELECT * FROM employee;

-- Update the record
UPDATE employee SET first_name = 'Jane' WHERE employee_id = 1;

-- Verify the updated timestamp
SELECT * FROM employee;

Flowchart:

Explanation:

The said code in PL/SQL that demonstrates how to utilize a PL/SQL trigger to maintain an automatically updated timestamp column in a table whenever rows are updated in the employee table.

A trigger update_last_modified is created using the BEFORE UPDATE ON employee syntax. This trigger fires before an UPDATE operation is executed on the employee table, and it operates on each row that is being updated. The purpose of this trigger is to automatically update the last_modified timestamp to the current timestamp using the SYSTIMESTAMP function.

A sample record is inserted into the employee table.

An UPDATE statement is executed to change the first_name of the record with employee_id 1 to 'Jane'. The trigger fires before this update, and the last_modified timestamp is automatically updated.

The contents of the employee table are queried again to verify that the last_modified timestamp has been automatically updated due to the trigger.

Previous: PL/SQL Trigger Exercises Home.
Next: Restricting column updates during specific hours using PL/SQL triggers.