Page 121 - Computer Software Application TP - Volume 1

COMPUTER SOFTWARE APPLICATION - CITS

This table has an id column as the primary key, a data column, and a created at column with a default value set
to the current timestamp.
3 Create a Table Level Trigger –
Now, let’s create a trigger that updates the created at timestamp whenever a new record is inserted:

DELIMITER //
CREATE TRIGGER update created at
BEFORE INSERT ON example table
FOR EACH ROW

SET NEW.created at = IFNULL(NEW.created at, CURRENT TIMESTAMP);
//
DELIMITER ;

This trigger is named update created at and is set to execute BEFORE INSERT on the example table. It
updates the created at column with the current timestamp if the value is NULL.

4 Insert a Record –
Now, let’s insert a record into the table:
INSERT INTO example table (data) VALUES (‘Example Data’);
View The Updated Record –

Now, retrieve the record to see the effect of the trigger:
SELECT * FROM example table;
You should see the created at column automatically updated with the current timestamp.

5 DROP the Trigger:
If you want to remove the trigger, you can use the following command:

DROP TRIGGER IF EXISTS update created at;
This step is optional and can be done if you no longer need the trigger.

106
CITS : IT & ITES - Computer Software Application - Exercise 32