Oracle and MySQL triggers

Triggers are database objects that encapsulate procedural logic, facilitating data validation, auditing, and maintaining referential integrity constraints. System administrators, database developers, and data engineers may require triggers to enforce business rules, log data changes, or propagate updates across related tables. The following sections provide detailed guidance on creating, managing, and testing triggers within the context of Amazon DMS.

Oracle usage

A trigger is a named program that is stored in the database and fired when a specified event occurs. The associated event causing a trigger to run can either be tied to a specific database table, database view, database schema, or the database itself.

Triggers can be run after:

Trigger types

Examples

Create a trigger that runs after a row is deleted from the PROJECTS table, or if the primary key of a project is updated.

CREATE OR REPLACE TRIGGER PROJECTS_SET_NULL
  AFTER DELETE OR UPDATE OF PROJECTNO ON PROJECTS
  FOR EACH ROW
  BEGIN
    IF UPDATING AND :OLD.PROJECTNO != :NEW.PROJECTNO OR DELETING THEN
      UPDATE EMP SET EMP.PROJECTNO = NULL
      WHERE EMP.PROJECTNO = :OLD.PROJECTNO;
    END IF;
END;
/

Trigger created.

DELETE FROM PROJECTS WHERE PROJECTNO=123;

SELECT PROJECTNO FROM EMP WHERE PROJECTNO=123;

PROJECTNO
NULL

Create a SYSTEM or schema trigger on a table. The trigger fires if a DDL DROP command runs for an object in the HR schema. It prevents dropping the object and raises an application error.

CREATE OR REPLACE TRIGGER PREVENT_DROP_TRIGGER
  BEFORE DROP ON HR.SCHEMA
  BEGIN
    RAISE_APPLICATION_ERROR (num => -20000,
    msg => 'Cannot drop object');
END;
/

Trigger created.

DROP TABLE HR.EMP

ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20000: Cannot drop object
ORA-06512: at line 2

For more information, see CREATE TRIGGER Statement in the Oracle documentation.

MySQL usage

MySQL supports triggers, but not all of the functionality provided by Oracle. Triggers are associated with users for privileges reasons and with specific tables. Triggers fire at the row level, and not at the statement level. You can modify MySQL triggers using a FOLLOWS or PRECEDES clause. Also, MySQL triggers can be chained using the FOLLOWS or PRECEDES clauses.

Syntax

CREATE
[DEFINER = { user | CURRENT_USER }]
TRIGGER trigger_name
trigger_time trigger_event
ON tbl_name FOR EACH ROW
[trigger_order]
trigger_body
trigger_time: { BEFORE | AFTER }
trigger_event: { INSERT | UPDATE | DELETE }
trigger_order: { FOLLOWS | PRECEDES } other_trigger_name

Examples

Create a trigger referencing the OLD and NEW values.

set delimiter /
CREATE OR REPLACE TRIGGER PROJECTS_SET_NULL
BEFORE UPDATE ON PROJECTS
FOR EACH ROW
BEGIN
IF OLD.PROJECTNO != NEW.PROJECTNO THEN
UPDATE EMP SET EMP.PROJECTNO = NULL
WHERE EMP.PROJECTNO = OLD.PROJECTNO;
END IF;
END;
/
set delimiter ;
UPDATE PROJECTS WHERE PROJECTNO=123;
SELECT PROJECTNO FROM EMP WHERE PROJECTNO=123;
PROJECTNO
----------
NULL

Drop a trigger.

DROP TRIGGER PROJECTS_SET_NULL

Summary

CREATE OR REPLACE TRIGGER check_update
BEFORE UPDATE ON projects
FOR EACH ROW
BEGIN
  /*Trigger body*/
END;
/

CCREATE TRIGGER check_update
BEFORE UPDATE ON projects
FOR EACH ROW
BEGIN
  /*Trigger body*/
END;
/

CREATE OR REPLACE TRIGGER check_update
BEFORE UPDATE ON projects
BEGIN
  /*Trigger body*/
END;
/

CREATE OR REPLACE TRIGGER drop_trigger
BEFORE DROP ON hr.SCHEMA
BEGIN
RAISE_APPLICATION_ERROR (
  num => -20000,
  msg => 'Cannot drop object');
END;
/

CREATE OR REPLACE TRIGGER Upper-NewDeleteOld
BEFORE INSERT OR UPDATE
OF first_name ON employees
FOR EACH ROW
BEGIN
:NEW.first_name := UPPER(:NEW.first_name);
:NEW.salary := :OLD.salary;
END;
/

CREATE TRIGGER UpperNewDeleteOld
BEFORE UPDATE ON empys
FOR EACH ROW SET
NEW.first_name = UPPER(NEW.first_name),
NEW.salary = OLD.salary;
END;
/

CREATE TRIGGER register_shutdown
ON DATABASE SHUTDOWN
BEGIN
Insert into logging values
  ('DB was shut down', sysdate);
commit;
END;
/

DROP TRIGGER last_name_change_trg;

DROP TRIGGER last_name_change_trg;

CREATE OR REPLACE TRIGGER
UpperNewDeleteOld
BEFORE INSERT OR UPDATE OF
first_name ON employees
FOR EACH ROW
BEGIN
  <<NEW CONTENT>>
END;
/

ALTER TRIGGER UpperNewDeleteOld
ENABLE;

ALTER TRIGGER UpperNewDeleteOld
DISABLE;

For more information, see Trigger Syntax and Examples and CREATE TRIGGER Statement in the MySQL documentation.