Triggers - Oracle to Aurora MySQL Migration Playbook
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Triggers

Feature compatibility Amazon SCT / Amazon DMS automation level Amazon SCT action code index Key differences

Three star feature compatibility

Three star automation level

MySQL doesn’t support statement and system event triggers. Also, MySQL doesn’t support CREATE OR REPLACE.

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:

  • Data Manipulation Language (DML) statements such as DELETE, INSERT, or UPDATE.

  • Data Definition Language (DDL) statements such as CREATE, ALTER, or DROP.

  • Database events and operations such as SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN.

Trigger Types

  • DML triggers can be created on tables or views and fire when inserting, updating, or deleting data. Triggers can fire before or after DML command run.

  • INSTEAD OF triggers can be created on a non-editable view. INSTEAD OF triggers provide an application-transparent method for modifying views that can’t be modified by DML statements.

  • SYSTEM event triggers are defined at the database or schema level including triggers that fire after specific events:

    • User log-on and log-off.

    • Database events such as startup or shutdown, DataGuard events, server errors.

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

Trigger Oracle MySQL

Before update trigger, row level

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;
/

Before update trigger, statement level

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

Not supported

System or event trigger

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

Not supported

Referencing :old and :new values in triggers

Use :NEW and :OLD in trigger body:

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;
/

Use NEW and OLD in trigger body:

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

Database event level trigger

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

Not supported

Drop a trigger

DROP TRIGGER last_name_change_trg;
DROP TRIGGER last_name_change_trg;

Modify logic run by a trigger

Can be used with create or replace

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

Not supported

Enable a trigger

ALTER TRIGGER UpperNewDeleteOld
ENABLE;

Not supported. Can be achieved by setting variables for each trigger to determine if it is turned off or turned on, and then checking the variable in an IF statement.

Disable a trigger

ALTER TRIGGER UpperNewDeleteOld
DISABLE;

Not supported. Can be achieved by setting variables for each trigger to determine if it is turned off or turned on, and then checking the variable in an IF statement.

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