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.
Feature compatibility | Amazon SCT / Amazon DMS automation level | Amazon SCT action code index | Key differences |
---|---|---|---|
|
|
MySQL doesn’t support statement and system event triggers. Also, MySQL doesn’t support |
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
, orUPDATE
. -
Data Definition Language (DDL) statements such as
CREATE
,ALTER
, orDROP
. -
Database events and operations such as
SERVERERROR
,LOGON
,LOGOFF
,STARTUP
, orSHUTDOWN
.
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
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 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 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 |
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 |
For more information, see Trigger Syntax and Examples