Database Triggers Oracle Database PL/SQL 10g Programming

1. Database TriggersOracle Database PL/SQL 10g Programming Chapter 10

2. Database Triggers • Database Trigger Concepts • DDL Triggers • DML Triggers • Instead-of Triggers • System or Database Event Triggers Oracle Database PL/SQL 10g Programming (Chapter 10)

3. Database TriggersDatabase Triggers: Definition • Database triggers are specialized stored programs that are triggered by events in the database. • Database triggers run between the time you issue a command and the time you perform the database management system action. • Database triggers cannot use SQL Data Control Language, like the SAVEPOINT, ROLLBACK, COMMIT, or SET TRANSACTION commands because the actual triggering events own transaction control. Oracle Database PL/SQL 10g Programming (Chapter 10)

4. Database TriggersDatabase Triggers: Restrictions • Database triggers have a separate name space from tables, views and stored programs, which means trigger names may duplicate names used for other objects. • Database triggers cannot declare variables using the SQL LONG or LONG RAW data types. • Database triggers can reference and use but not modify LOB (large object) and user-defined object types. • Database triggers cannot write to tables currently in the midst of a transaction, which would be a mutating tablefrom the perspective of the trigger – one that’s data are in the process of changing. Oracle Database PL/SQL 10g Programming (Chapter 10)

5. Database TriggersDDL Triggers: Definition • DDL triggers fire when you CREATE, ALTER, RENAME, or DROP objects in a database schema. • DDL triggers monitor poor programming practices, such as when programs create and drop temporary tables rather than use Oracle collections effectively in memory. NOTE: Temporary tables can fragment disk space and degrade database performance over time. Oracle Database PL/SQL 10g Programming (Chapter 10)

6. Database TriggersDDL Triggers: Prototype CREATE [OR REPLACE] TRIGGER trigger_name{BEFORE | AFTER} ddl_event ON {DATABASE | SCHEMA}[WHEN (logical_expression)][DECLAREdeclaration_statements;]BEGINexecution_statements;END [trigger_name];/; Oracle Database PL/SQL 10g Programming (Chapter 10)

7. Database TriggersDML Triggers: Definition • DML triggers fire when you INSERT, UPDATE, or DELETE data from a table. • DML triggers audit, check, save, and replace values before they are changed. • DML tirggers have two varieties with or without the WHEN clause: • Statement-level triggers fire whenever an event occurs. • Row-level triggers fire when an event occurs and a condition met. Oracle Database PL/SQL 10g Programming (Chapter 10)

8. Database TriggersDML Triggers: Definition • DML triggers have access to pseudo column copies of transactions, known as the new and old column copies: • The new and old pseudo columns are accessed as local variables in the WHEN clause of triggers. • The :new and :old pseudo columns are accessed in the body of triggers because their variable scope is outside of the body scope. • The REFERENCING clause enables changing the names of the new and old pseudo columns: REFERENCING [new ASother_new][old ASother_old] Oracle Database PL/SQL 10g Programming (Chapter 10)

9. Database TriggersDML Triggers: Prototype CREATE [OR REPLACE] TRIGGER trigger_name {BEFORE | AFTER}{INSERT | UPDATE | UPDATE OF column1 [, column2 [, column(n+1)]] | DELETE}[FOR EACH ROW][WHEN (logical_expression)][DECLAREdeclaration_statements;]BEGINexecution_statements;END [trigger_name];/ Oracle Database PL/SQL 10g Programming (Chapter 10)

10. Database TriggersInstead-of Triggers: Definition • Instead-of triggers redirect ordinary DML commands to perform an alternate action. • Instead-of triggers typically are used to redirect action from non-updateable views to actual tables. • Instead-of triggers are performed when an INSERT, UPDATE, or DELETE statement attempts to change values. • Instead-of triggers have access to pseudo column copies of transactions, known as the new and old column copies: • The new and old pseudo columns are accessed as local variables in the WHEN clause of triggers. • The :new and :old pseudo columns are accessed in the body of triggers because their variable scope is outside of the body scope. Oracle Database PL/SQL 10g Programming (Chapter 10)

11. Database TriggersInstead-of Triggers: Definition • Instead-of triggers have access to pseudo column copies of transactions, known as the new and old column copies: • The new and old pseudo columns are accessed as local variables in the WHEN clause of triggers. • The :new and :old pseudo columns are accessed in the body of triggers because their variable scope is outside of the body scope. • The REFERENCING clause enables changing the names of the new and old pseudo columns: REFERENCING [new ASother_new][old ASother_old] Oracle Database PL/SQL 10g Programming (Chapter 10)

12. Database TriggersInstead-of Triggers: Prototype CREATE [OR REPLACE] TRIGGER trigger_nameINSTEAD OF {dml_statement | ddl_statement}ON {object_name | database | schema}FOR EACH ROW[WHEN (logical_expression)][DECLAREdeclaration_statements;]BEGINexecution_statements;END [trigger_name];/ Oracle Database PL/SQL 10g Programming (Chapter 10)

13. Database TriggersSystem or Database Event Triggers: Definition • System or database event triggers audit server startup and shutdown, and user logon and logoff activities. • System or database event triggers monitor: • The duration of connections by users. • The uptime of the database server. Oracle Database PL/SQL 10g Programming (Chapter 10)

14. Database TriggersSystem or Database Event Triggers: Prototype CREATE [OR REPLACE] TRIGGER trigger_name{BEFORE | AFTER} database_event ON {database | schema}[DECLARE]declaration_statements;BEGINexecution_statements;END [trigger_name];/ Oracle Database PL/SQL 10g Programming (Chapter 10)

15. Summary • Database Trigger Concepts • DDL Triggers • DML Triggers • Instead-of Triggers • System or Database Event Triggers Oracle Database PL/SQL 10g Programming (Chapter 10)