Ch 9: Database Triggers

All of the following would cause a trigger to fire, except ____. BEFORE INSERT DELETE UPDATE

BEFORE

CURSOR basketitem_curIS SELECT idproduct, quantity, option1 FROM bb_basketitem WHERE idbasket = :NEW.idbasket; In the code fragment above, which of the following represents the correlation identifier? idproduct bb_basketitem quantity :NEW.idbasket

:NEW.idbasket

Which of the following statements is correct? If multiple triggers exist on a table, the order in which the triggers will be fired can be set. A BEFORE statement level trigger will fire before a BEFORE row level trigger. If you have two statement level triggers, the order in which they are fired is dependent on the order in which they were written. Only one trigger can be constructed per table.

A BEFORE statement level trigger will fire before a BEFORE row level trigger.

The statement ____ recompiles the trigger named sales_trg. ENABLE TRIGGER sales_trg COMPILE; DROP TRIGGER sales_trg COMPILE; COMPILE TRIGGER sales_trg COMPILE; ALTER TRIGGER sales_trg COMPILE;

ALTER TRIGGER sales_trg COMPILE;

The statement ____ can be used to disable or enable a specific trigger. ANALYZE TRIGGER trigger_name DISABLE|ENABLE; ALTER TRIGGER trigger_name DISABLE|ENABLE; AUDIT TRIGGER trigger_name DISABLE|ENABLE; GRANT TRIGGER trigger_name DISABLE|ENABLE;

ALTER TRIGGER trigger_name DISABLE|ENABLE;

The ____ trigger clause allows a trigger to include multiple triggering events in a single trigger. MULTIPLE MULTIPLE TRIGGER COMPOUND COMPOUND TRIGGER

COMPOUND TRIGGER

The statement ____ correctly deletes a trigger. DEL TRIGGER trigger_name; DELETE TRIGGER trigger_name; REMOVE TRIGGER trigger_name; DROP TRIGGER trigger_name;

DROP TRIGGER trigger_name;

A constraining table is one that is being modified by a DML action when a trigger is fired.

False

LOB and OBJECT columns can be referenced in a trigger and can also be modified.

False

LONG or LONG RAW variables can be declared in triggers and the NEW and OLD qualifiers cannot refer to these types of columns.

False

Only one triggering event can be addressed in a single trigger.

False

System triggers are typically used to allow the modification of data through a view on a view that is nonmodifiable.

False

The default timing of a trigger is row level and no code is included to achieve this timing.

False

The timing of triggers must be indicated as either BEGIN or END.

False

To remove a trigger from the system, the DEL TRIGGER statement is used.

False

Using the MULITPLE TRIGGER clause will allow more than one triggering event to be included in a single trigger.

False

Database system events include all of the following, except ____. LOGON SHUTDOWN GRANT STARTUP

GRANT

Which of the following indicates that the trigger is fired only once, regardless of the number of rows affected by the DML statement? Statement level Event level DML level Row level

Statement level

____ triggers refer to database triggers that are fired by Data Definition Language (DDL) statements or database system events rather than DML actions. ALTER CREATE System Instead-Of

System

A database trigger is tied to a database table or view and is implicitly fired by the database system when that table or view is affected with the associated DML action.

True

Correlation identifiers allow us to refer to and use the row data values of a DML action.

True

If multiple triggers exist on a table, there is no guarantee about the order in which the triggers will be fired.

True

LONG RAW variables cannot be declared in triggers.

True

Numerous triggers can be constructed on one table and, therefore, a DML action on that table can fire off more than one trigger.

True

One prominent limitation in the use of triggers is that they cannot issue transaction control statements of COMMIT, ROLLBACK, and SAVEPOINT.

True

The ALTER TRIGGER statement is used to recompile, enable, or disable a trigger.

True

The FOLLOWS trigger option enables a developer to control the firing order of triggers.

True

The WHEN clause of a trigger can be used only in row level triggers.

True

The trigger body is a PL/SQL block that contains the actions that take place when the trigger fires.

True

Which of the following events will cause the trigger to fire? AFTER UPDATE OF orderplaced ON bb_basket INSERT UPDATE DELETE AFTER

UPDATE

Row level options are only applicable for ____ events. CREATE INSERT DECLARE UPDATE and DELETE

UPDATE and DELETE

DDL events include all of the following, except ____. a. DROP b. GRANT c. DECLARE d. ALTER

c. DECLARE

Which of the following statements is true about mutating tables? a. A table in an UPDATE statement that fires a row level trigger affecting the same table is not considered a mutating table unless the UPDATE includes a subquery. b. A table in a DELETE statement that fires a row level trigger affecting the same table is not considered a mutating table unless the DELETE includes a subquery. c. A table in an INSERT statement that fires a row level trigger affecting the same table is not considered a mutating table unless the INSERT includes a subquery. d. A table in a DROP statement that fires a row level trigger affecting the same table is not considered a mutating table unless the DROP includes a subquery.

c. A table in an INSERT statement that fires a row level trigger affecting the same table is not considered a mutating table unless the INSERT includes a subquery.

Which trigger option will force the trigger to fire before the test_trg trigger. a. PRIOR test_trg b. no option is available to control the firing order c. PRECEDES test_trg d. BEFORE test_trg

c. PRECEDES test_trg

Which of the following statements is true? a. The body of a trigger cannot read or modify the primary, unique, or foreign key columns of a key-preserved table. b. The body of a trigger cannot read or modify the primary, unique, or foreign key columns of a conditional table. c. The body of a trigger cannot read or modify the primary, unique, or foreign key columns of a constraining table. d. The body of a trigger cannot read or modify the primary, unique, or foreign key columns of a mutating table.

c. The body of a trigger cannot read or modify the primary, unique, or foreign key columns of a constraining table.

A(n) ____ trigger fires whenever the event occurs, regardless of the schema in which it occurs. row level system level Instead-Of database level

database level

A(n) ____ table is one that is involved in a join and the keys of the original table are included in the keys of the resultant join.

key-preserved

CREATE OR REPLACE TRIGGER logon_trg AFTER logon ON SCHEMA BEGIN INSERT INTO bb_audit_logon VALUES (USER, SYSDATE); END; In the code fragment above, which of the ____ statement represents the system event. logon INSERT SYSDATE AFTER

logon

A(n) ____ is a table that is being modified by a DML action when a trigger is fired. system table mutating table transaction table correlation table

mutating table

The default timing of a trigger is ____. statement level system level row level header level

statement level

CREATE [OR REPLACE] TRIGGER trigger_name [BEFORE, AFTER] [List of DDL or Database System Events] [ON DATABASE | SCHEMA] Trigger body; The syntax above represents that of a(n) ____ trigger. statement row level system Instead-Of

system