Database Programming with PL/SQL
13-1: Introduction to Triggers
Practice Activities
Vocabulary
Execute automatically whenever a particular event occurs within an application. - Application triggers
Programs to execute a specific action whenever a specific event occurs in an application or in the database. - Triggers
Execute automatically whenever a data event (such as DML or DDL) or system event (such as logon or shutdown) occurs on a schema or database. Should be used sparingly so as to not slow down the database or create complex interdependencies. - Database triggers
Try It / Solve It
1. What are triggers commonly used for in a database?
Triggers are commonly used to:
Enhance complex database security rules
Create auditing records automatically
Enfore complex data integrity rules
Create logging records automatically
Prevent tables from being accidentally dropped
Prevent invalid DML transactions from occurring
Generate derived column values automatically
Maintain synchronous table replication
Gather statistics on table access
Modify table data when DML statements are issued against views
2. List the types of events that cause a database trigger to fire?
DML operations on a table or view
DDL statements (create, alter)
Database system events (user logs on, the DBA shuts down the database)
3. What is the most important thing to check before creating a database trigger?
We need tu assure we don't make a recursive trigger or cascading effects.
4. Describe four differences between triggers and procedures.
Defined with CREATE TRIGGER instead of CREATE PROCEDURE
Source code in USER_TRIGGERS instead of USER_SOURCE
Implicitly invoked instead of explicitly invoked
For triggers, COMMIT, SAVEPOINT and ROLLBACK are not allowed, for procedure are allowed.
0 Comments