SQL Reference

Example

CREATE TRIGGER example_trigger
BEFORE UPDATE ON student_table
FOR EACH ROW
BEGIN
   IF NEW.age < OLD.age THEN
      SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Age can not be reduced';
   END IF;
END;

Output

Query OK, 0 rows affected (0.01 sec)

Explanation

In this example, a trigger named ‘example_trigger’ is created on ‘student_table’. This trigger fires before an update operation on the ‘student_table’. If the new age value in the update statement is less than the old age value, the update operation is aborted and a message ‘Age can not be reduced’ is displayed. The ‘OLD’ keyword is used to refer to column values as they were before the UPDATE operation.

Example

CREATE TRIGGER sample_trigger
ON sample_table
FOR UPDATE
AS
BEGIN
  DECLARE @old_val varchar(50);

  SELECT @old_val = OLD.column_name
  FROM deleted;

  PRINT @old_val;
END;

Output

'old_value'

Explanation

The sample code demonstrates a trigger in SQL Server, which executes when an update event takes place on ‘sample_table’. The OLD statement is used to fetch the old value of ‘column_name’, that was present before the update operation. The old value is stored in the local variable ‘@old_val’, and then printed. Note however, that ‘OLD.column_name’ syntax as used in this example, works in MySQL, but is not directly applicable in SQL Server. In SQL Server, the ‘deleted’ pseudo table is used instead to access the old records.

Example

CREATE OR REPLACE TRIGGER before_employee_update
  BEFORE UPDATE
  ON employee
  FOR EACH ROW
BEGIN
  DBMS_OUTPUT.PUT_LINE('Old Salary: ' || :OLD.salary);
END;

Output

Old Salary: 50000

Explanation

In this example, a trigger is created with the name before_employee_update which fires before any update operation on the employee table. During the trigger action, it outputs the old salary of the employee using :OLD.salary statement, which refers to the state of the row before it was updated.