NEW

MySQL

Example

CREATE TRIGGER update_product_quantity
BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
   IF NEW.quantity < 0 THEN
      SET NEW.quantity = 0;
   END IF;
END;

Output

Trigger update_product_quantity is created with no return message.

Explanation

In the MySQL trigger, the NEW keyword is used to access the column of the new row being inserted or updated. The NEW.quantity represents the value of the quantity column of the new row that is being inserted or updated. In this example, a trigger is defined to update the quantity of products. If the new quantity value is less than 0, it is set to 0. This ensures there’s no negative value in the quantity column after an update.

PostgreSQL

Example

CREATE TRIGGER check_update
BEFORE UPDATE ON employees
FOR EACH ROW
  EXECUTE PROCEDURE trigger_function();

CREATE OR REPLACE FUNCTION trigger_function()
RETURNS trigger AS
$BODY$
BEGIN
  IF NEW.salary > OLD.salary THEN
      RAISE NOTICE 'Employee salary increased from % to %', OLD.salary, NEW.salary;
  ELSE
      RAISE NOTICE 'No change in salary';
  END IF;
  RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;

Output

NOTICE:  Employee salary increased from 50000 to 60000

Explanation

The NEW keyword in PostgreSQL refers to the new row being inserted or updated in a table when a trigger is activated. In the given example, a trigger function is defined, which gets executed before any update operation on the employees table. The function compares the new salary (NEW.salary) with the old salary (OLD.salary) and raises a notice accordingly. If the new salary is greater than the old one, it announces the change, else it announces that there was no change in salary.

Oracle

Example

CREATE OR REPLACE TRIGGER emp_after_insert
AFTER INSERT
ON employees
FOR EACH ROW

DECLARE
   v_username varchar2(100);
BEGIN
   SELECT user INTO v_username
   FROM dual;

   INSERT INTO emp_audit(emp_id, username, insert_date)
   VALUES (:NEW.emp_id, v_username, sysdate);
END;
/

Output

Trigger created.

Explanation

In this trigger, :NEW is a keyword that allows access to the new data that is being inserted into the employees table. The trigger is activated after a new record is inserted into the employees table. An entry in the emp_audit table is then created containing the new emp_id from the employees table, the current username, and the system date.