'The OLD and NEW keywords allow us to access columns in the rows affected by the trigger.'

As we know that in trigger definition we can refer to the column of the row being inserted, updated or deleted. Here's how the OLD and NEW keywords enable us to access columns:

• We must prepend an OLD qualifier to the column name to reference the value in the original row.

• We must precede the column name with the NEW qualifier to refer to the value in the new row.

Now, OLD and NEW must be used appropriately because the event is triggered

Determine what is allowed −

• In the INSERT trigger, NEW.column_name represents the column value to be inserted into the new row. OLD is not allowed here.

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

• In the UPDATE trigger, OLD.column_name and NEW.column_name refer to the values ​​of the columns in the row before and after the row is updated respectively.

In other words, we can say that OLD must be used in a read-only manner, while NEW can be used to read or change the value of a column.

Use keyword OLD in trigger

The following is an example of a DELETE trigger, showing how to use 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 keyword NEW

The following is an example of the INSERT trigger, showing how to use 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)

The above is the detailed content of 'The OLD and NEW keywords allow us to access columns in the rows affected by the trigger.'. For more information, please follow other related articles on the PHP Chinese website!