Introduction
- Named PL/SQL unit. Can be enabled or disabled, but cannot be explicitly invoked.
- Trigger is created on or defined on the item (to which it will be "attached"): table, view, schema or database.
- Firing criteria is based on a triggering event (DML, DDL, System) and on a timing specification (before, after, instead of). A conditional clause (WHEN) may also be used to further specify the triggering rules.
- Triggers do not accept arguments.
- Triggers can be written in
PL/SQL
orJAVA
. - Starting on Oracle 11g, triggers can now be created in the disabled state.
Triggers: what for?
Customization of database management; centralization of some business or validation rules; logging and audit.
- Overcome the mutating-table error.
- Maintain referential integrity between parent and child.
- Generate calculated column values
- Log events (connections, user actions, table updates, etc)
- Gather statistics on table access
- Modify table data when DML statements are issued against views
- Enforce referential integrity when child and parent tables are on different nodes of a distributed database
- Publish information about database events, user events, and SQL statements to subscribing applications
- Enforce complex security authorizations: (i.e. prevent DML operations on a table after regular business hours)
- Prevent invalid transactions
- Enforce complex business or referential integrity rules that you cannot define with constraints
- Control the behavior of DDL statements, as by altering, creating, or renaming objects
- Audit information of system access and behavior by creating transparent logs
- if the trigger code turns out to be very(?) long, you will more likely have better performance using a stored procedure instead. In fact, a trigger cannot be larger than 32Kb (because stored on LONG column). If you need to write something longer, use a stored procedure instead.
- You can’t control the sequence of or synchronize calls to triggers, and this can present problems if you rely too heavily on triggers
- A trigger can call a SQL statement that in turn fires another trigger: The number of cascading triggers is limited to 32, after which an exception is thrown. (11g and earlier)
Triggers: How much is too much?
- DML statements on tables with DML Triggers are likely to have decreased perform.
- You may choose to disable triggers before loading data. Of course the cost to this is the work you'll have to perform latter what the disabled triggers did not do.
- If the task is complex, you may spread it across multiple triggers. However, this will make it maintenance more difficult, since it is likely to make the entire process harder to follow later.
- Triggers may get disabled by accident: For example, DDLs on objects touched by a trigger may render it unusable. If you don't catch this, you may end up with missing/corrupt data.
Five types of Triggers | |
---|---|
DDL Triggers |
|
DML Triggers |
|
Compound Triggers |
|
Instead-of Triggers |
|
System Database event Triggers |
|
Constraints |
|
Triggers |
|
Using DML triggers |
Using Compound triggers (11g only) |
Using triggers on object tables |
OBJECT_VALUE.
Check an example here.Using INSTEAD OF triggers |
INSTEAD OF
triggers are created on views. This allow DML statements to be issued against non-updatable views. Check an example here.Privileges required to use Triggers |
---|
|
Trigger Design Guidelines
- Do not create triggers that duplicate database features. For example, do not create a trigger to reject invalid data if you can do the same with constraints.
- Do not create triggers that depend on the order in which a SQL statement processes rows (which can vary).
For example, do not assign a value to a global package variable in a row trigger if the current value of the variable depends on the row being processed by the row trigger. If a trigger updates global package variables, initialize those variables in aBEFORE
statement trigger. - If the triggering statement of a
BEFORE
statement trigger is anUPDATE
orDELETE
statement that conflicts with anUPDATE
statement that is running, then the database does a transparentROLLBACK
toSAVEPOINT
and restarts the triggering statement. The database can do this many times before the triggering statement completes successfully. Each time the database restarts the triggering statement, the trigger fires. TheROLLBACK
toSAVEPOINT
does not undo changes to package variables that the trigger references. To detect this situation, include a counter variable in the package. - Do not create recursive triggers. The trigger fires recursively until it runs out of memory.
- If you create a trigger that includes a statement that accesses a remote database, then put the exception handler for that statement in a stored subprogram and invoke the subprogram from the trigger.
- Use
DATABASE
triggers judiciously. They fire every time any database user initiates a triggering event.
- Only committed triggers fire. A trigger is committed, implicitly, after the
CREATE
TRIGGER
statement that creates it succeeds.
Trigger Restrictions
- Maximum Trigger Size
- Max 32Kb. If needed, you can move code into functions, procedures or packages. In this case, the code could also be reused. stored modules can also be wrapped.
- If the logic for your trigger requires much more than 60 lines of PL/SQL source text, then put most of the source text in a stored subprogram and invoke the subprogram from the trigger
- DCL and DDL Restrictions
- Only an autonomous trigger can run TCL or DDL statements
- Nonsystem trigger bodies can’t contain DDL statements. They also can’t contain Transaction Control Language (TCL) commands, like ROLLBACK, SAVEPOINT,or COMMIT.
- A trigger cannot invoke a subprogram that runs transaction control statements, because the subprogram runs in the context of the trigger body.
- If you declare a trigger as autonomous, nonsystem trigger bodies can contain Data Control Language commands because they don’t alter the transaction scope.
- To enable a trigger to work outside the scope of a triggering statement you use include in its DECLARE block:
PRAGMA AUTONOMOUS_TRANSACTION;
- A larger problem with SQL statements exists with remote transactions. If you call a remote
schema-level function or procedure from a trigger body, it is possible that you may encounter a
timestamp or signature mismatch. A mismatch invalidates the trigger and causes the triggering
SQL statement to fail. - LONG and LONG RAW Datatypes
- The LONG and LONG RAW datatypes are legacy components. Migrate out of them.
- A trigger cannot declare a variable of the LONG or LONG RAW data type.
- You may, however, insert into a LONG or LONG RAW column when the value can be converted CHAR or VARCHAR2.
- Row-level triggers cannot use a :new,:old or parent with a LONG or LONG RAW column.
- Triggers will fail if try to access a mutating table.
- Oracle 11g has relaxed some mutating table restrictions
Triggers and data transfers |
---|
These utilities may fire triggers:SQL*LOoader (sqlldr), Data Pump Import (impdp) and Original import (imp) |
SQL*Loader (sqlldr) :- During a SQL*Loader conventional load, INSERT triggers fire. - Before a SQL*Loader direct load, triggers are disabled. |
Data Pump Import (impdp) :- If a table to be imported does not exist on the target database, or if you specify TABLE_EXISTS_ACTION=REPLACE, then impdp creates and loads the table before creating any triggers, so no triggers fire. - If a table to be imported exists on the target database, and you specify either TABLE_EXISTS_ACTION=APPEND or TABLE_EXISTS_ACTION=TRUNCATE, then impdp loads rows into the existing table, and INSERT triggers created on the table fire. |
Original Import (imp) :- If a table to be imported does not exist on the target database, then imp creates and loads the table before creating any triggers, so no triggers fire. - If a table to be imported exists on the target database, then the Import IGNORE parameter determines whether triggers fire during import operations: - If IGNORE=n (default), then imp does not change the table and no triggers fire. - If IGNORE=y, then imp loads rows into the existing table, and INSERT triggers created on the table fire. |