How OLD and NEW keywords enable us to access columns in row affected by
a trigger?

As we know that in trigger definition, we can refer to columns of the row being inserted, updated or deleted. Following are the ways OLD and NEW keywords enable us to access columns −

• We must have to prefix the column name with a qualifier of OLD to refer to a value from the original row.

• We must have to prefix the column name with a qualifier of NEW to refer to a value in the new row.

Now, the use of OLD and NEW must be done appropriately because the triggering event

Determines which of them are allowable −

• In an INSERT trigger, NEW.column_name indicates a column value to be inserted into a new row. Here OLD is not allowable.

• In a DELETE trigger, OLD.column_name indicates the value of a column in a row to be deleted. Here NEW is not allowable.

• In an UPDATE trigger, OLD.column_name and NEW.column_name refer to the value of the column in a row before and after the row is updated respectively.

In other words, we can say that OLD must be used in read-only fashion and NEW can be used to read or change column values.

Use of keyword OLD in trigger

Following the example of DELETE trigger is showing the use of OLD −

mysql> CREATE TRIGGER studentinfo_after_delete
   -> AFTER DELETE
   -> ON student_info
   -> FOR EACH ROW FOLLOWS
   -> BEGIN
   -> DECLARE vuser varchar(30);
   -> SELECT USER() into vuser;
   -> INSERT INTO student_info_deleted(id,deleted_date,deleted_by) VALUES(OLD.id,SYSDATE(),vuser);
   -> END; //
Query OK, 0 rows affected (0.25 sec)

Use of keyword NEW in trigger

Following the example of INSERT trigger is showing the use of NEW −

mysql> Create Trigger before_inser_studentage BEFORE INSERT ON
student_age FOR EACH ROW
BEGIN
IF NEW.age < 0 THEN SET NEW.age = 0;
END IF;
END //
Query OK, 0 rows affected (0.30 sec)

Sai Subramanyam

e

Updated on: 30-Jul-2019

510 Views

Kickstart Your Career

Get certified by completing the course

Get Started