PL/SQL Section 13 Quiz

1. Which command would you use to see if your triggers are enabled or disabled?

Tandai untuk Ditinjau

(1) Point

SELECT trigger_name, status
FROM USER_TRIGGERS; (*)

SELECT trigger_name, trigger_type
FROM USER_TRIGGERS;

SELECT object_name, status
FROM USER_OBJECTS
WHERE object_type = 'TRIGGER';

DESCRIBE TRIGGER

Incorrect. Refer to Section 13 Lesson 5.

---

2. After the following SQL statement is executed, all the triggers on the DEPARTMENTS table will no longer fire, but will remain in the database. True or False?

ALTER TABLE departments DISABLE ALL TRIGGERS;

Tandai untuk Ditinjau

(1) Point

True (*)

False

Correct

---

3. A user creates the following trigger:

CREATE OR REPLACE TRIGGER emp_trigg
AFTER DELETE ON employees
BEGIN
    ...
END;

The user now tries to drop the EMPLOYEES table. What happens?

Tandai untuk Ditinjau

(1) Point

Both the table and the trigger are dropped. (*)

The table is dropped and the trigger is disabled.

The table is dropped but the trigger is not dropped.

An error message is displayed because you cannot drop a table that is referenced by a trigger.

Correct

---

4. The following objects have been created in a user's schema:
- A function FUNC1
- A package PACK1 which contains a public procedure PACKPROC and a private function PACKFUNC
- A trigger TRIGG1.
The procedure and functions each accept a single IN parameter of type NUMBER, and the functions return BOOLEANs. Which of the following calls to these objects (from an anonymous block) are correct? (Choose two.)

Tandai untuk Ditinjau

(1) Point

trigg1;

pack1.packproc(25);(*)

IF pack1.packfunc(40) THEN ...

SELECT func1(100) FROM dual;

IF func1(75) THEN ...(*)

Incorrect. Refer to Section 13 Lesson 1.

---

5. Which of the following best describes a database trigger?

Tandai untuk Ditinjau

(1) Point

A subprogram that is invoked explicitly by the calling application

A PL/SQL subprogram that always returns exactly one value

A subprogram that checks whether a user has typed the correct password to log on to the database

A PL/SQL subprogram that executes automatically whenever an associated database event occurs (*)

A PL/SQL subprogram that inserts rows into a logging table

6. You can use a database trigger to prevent invalid transactions from being committed. True or False?

Tandai untuk Ditinjau

(1) Point

True (*)

False

Correct

---

7. The following code will successfully create emp_trigg: True or False?

CREATE OR REPLACE TRIGGER emp_trigg
BEFORE DELETE OF salary ON employees
BEGIN
    RAISE_APPLICATION_ERROR(-20202,'Deleting salary is not allowed');
END;

Tandai untuk Ditinjau

(1) Point

True

False (*)

Incorrect. Refer to Section 13 Lesson 2.

---

8. You need to create a trigger that will fire whenever an employee's salary or job_id is updated, but not when any other column of the EMPLOYEES table is updated. Which of the following is the correct syntax to do this?

Tandai untuk Ditinjau

(1) Point

CREATE TRIGGER emp_upd_trigg
AFTER UPDATE OF (salary, job_id) ON employees
BEGIN ...

CREATE TRIGGER emp_upd_trigg
AFTER UPDATE ON employees (salary, job_id)
BEGIN ...

CREATE TRIGGER emp_upd_trigg
AFTER UPDATE OF salary, job_id ON employees
BEGIN ... (*)

CREATE TRIGGER emp_upd_trigg
AFTER UPDATE OF salary OR job_id ON employees
BEGIN ...

Incorrect. Refer to Section 13 Lesson 2.

---

9. What is wrong with the following code?

CREATE OR REPLACE TRIGGER loc_trigg
BEFORE DELETE ON locations
BEGIN
    RAISE_APPLICATION_ERROR(-20201,'Invalid delete');
    ROLLBACK;
END;

Tandai untuk Ditinjau

(1) Point

The last line should be:
END loc_trigg;

The second line should be:
BEFORE DELETE OF locations

Nothing is wrong, this trigger will compile and execute successfully.

You cannot use RAISE_APPLICATION_ERROR inside a trigger.

You cannot use ROLLBACK inside a trigger. (*)

Incorrect. Refer to Section 13 Lesson 2.

---

10. Examine this code:

CREATE TRIGGER new_trigg
AFTER CREATE ON reserved_word
BEGIN ...

Which of the following can be used in place of reserved_word? (Choose two.)

Tandai untuk Ditinjau

(1) Point

USER

DATABASE(*)

SCHEMA(*)

TABLE employees

TABLE

11. Mutating table errors can be caused by DML triggers, but not by database event triggers. True or False?

Tandai untuk Ditinjau

(1) Point

True (*)

False

Correct

---

12. What is wrong with the following code?

CREATE OR REPLACE TRIGGER call_trigg
AFTER UPDATE OR DELETE ON employees
BEGIN
    CALL del_emp_proc
END;

Tandai untuk Ditinjau

(1) Point

When using CALL, only one DML statement can be tested, so UPDATE OR DELETE is wrong.

The CALL statement should end with a semicolon (;)

When CALL is used, the BEGIN and END; statements should be omitted. (*)

You cannot use a CALL statement in a DML trigger.

Incorrect. Refer to Section 13 Lesson 4.

---

13. What is wrong with the following code example for a compound trigger?

CREATE OR REPLACE TRIGGER log_emps
FOR UPDATE OF salary ON employees
COMPOUND TRIGGER
 TYPE t_log_emp IS TABLE OF log_table%ROWTYPE
 INDEX BY BINARY_INTEGER;
log_emp_tab t_log_emp;

AFTER EACH ROW IS
BEGIN
-- some action
END AFTER EACH ROW;

AFTER STATEMENT IS
BEGIN
-- some action
END AFTER STATEMENT;
END log_emps;

Tandai untuk Ditinjau

(1) Point

The declaration section is missing the DECLARE keyword. (*)

The COMPOUND TRIGGER statement is missing IS.

There is nothing wrong with this example.

The triggering event FOR UPDATE is not allowed.

The order of the timing statements is reversed.

Correct

---

14. What is wrong with the following code?

CREATE TRIGGER dept_trigg
BEFORE UPDATE OF department_name ON departments
BEGIN
    DBMS_OUTPUT.PUT_LINE(:NEW.department_name);
END;

Tandai untuk Ditinjau

(1) Point

The second line should be:
BEFORE UPDATE ON departments.department_name

You cannot use DBMS_OUTPUT.PUT_LINE inside a trigger.

You cannot use :NEW or :OLD in a statement trigger. (*)

You cannot use :NEW in a BEFORE trigger, only in an AFTER trigger.

Incorrect. Refer to Section 13 Lesson 3.

---

15. With which kind of trigger can the :OLD and :NEW qualifiers be used?

Tandai untuk Ditinjau

(1) Point

Database Event triggers

DDL triggers

Statement triggers

Row triggers (*)

AFTER triggers

 1. Which of the following could NOT be done by a database trigger?

Tandai untuk Ditinjau

(1) Point

Ensuring that a student never arrives late for a class (*)

Enforcing a complex business rule

Keeping a log of how many rows have been inserted into a table

Recalculating the total salary bill for a department whenever an employee's salary is changed

Enforcing a complex database security check

Correct

---

2. Which of the following are good guidelines to follow when creating a database trigger? (Choose two.)

Tandai untuk Ditinjau

(1) Point

Do not create a trigger that automatically fires another trigger.(*)

Do not use a trigger to replace or duplicate something which the Oracle Server does automatically.(*)

Use triggers to prevent unauthorized users from SELECTing confidential data.

Use triggers to override privilege checking and view other users' private tables.

Where possible, use a trigger to enforce a foreign key constraint.

Correct

---

3. What type of database object would you create to write an auditing record automatically every time a user connects to the database?

Tandai untuk Ditinjau

(1) Point

A complex view

A procedure

A trigger (*)

A package

A function

Incorrect. Refer to Section 13 Lesson 1.

---

4. Examine this code:

CREATE TRIGGER de_trigg
    -- Line A
BEGIN ...

Which of the following are NOT valid at Line A ? (Choose two.)

Tandai untuk Ditinjau

(1) Point

AFTER LOGOFF ON SCHEMA(*)

BEFORE LOGOFF ON SCHEMA

BEFORE DISCONNECT ON SCHEMA(*)

AFTER SERVERERROR ON SCHEMA

AFTER LOGON ON SCHEMA

Incorrect. Refer to Section 13 Lesson 4.

---

5. What is wrong with the following code?

CREATE TRIGGER call_trigg
AFTER LOGOFF ON SCHEMA
BEGIN
    CALL drop_proc;
END;

Tandai untuk Ditinjau

(1) Point

All of these. (*)

The CALL statement must not end with a semicolon (;)

When using CALL, you must not code END;

When using CALL, you must not code BEGIN

You cannot code an AFTER LOGOFF trigger

6. The database administrator wants to write a log record every time an Oracle Server error occurs in any user's session. The DBA creates the following trigger:

CREATE TRIGGER log_errs_trigg
    -- Line A
BEGIN
    INSERT INTO errlog_table VALUES (...);
END;
What should the DBA code at Line A ?

Tandai untuk Ditinjau

(1) Point

AFTER SERVER ERROR ON DATABASE

AFTER SERVERERROR ON SCHEMA

AFTER ORACLE ERROR ON SCHEMA

AFTER SERVERERROR ON DATABASE (*)

AFTER ERROR ON DATABASE

Incorrect. Refer to Section 13 Lesson 4.

---

7. You need to disable all triggers that are associated with DML statements on the DEPARTMENTS table. Which of the following commands should you use?

Tandai untuk Ditinjau

(1) Point

DISABLE ALL TRIGGERS ON departments;

ALTER TABLE departments DISABLE ALL TRIGGERS; (*)

ALTER TRIGGER DISABLE ALL ON departments;

ALTER TABLE departments DISABLE TRIGGERS;

ALTER TABLE departments DROP ALL TRIGGERS;

Incorrect. Refer to Section 13 Lesson 5.

---

8. User AYSEGUL successfully creates the following trigger:

CREATE TRIGGER loc_trigg
BEFORE UPDATE ON aysegul.locations
BEGIN ....

AYSEGUL now tries to drop the LOCATIONS table. What happens?

Tandai untuk Ditinjau

(1) Point

Both the table and the trigger are dropped. (*)

The table is dropped and the trigger is disabled.

The trigger is dropped but the table is not dropped.

None of these.

An error message is displayed because you cannot drop a table that is associated with a trigger.

Correct

---

9. Which dictionary view would you query to see the detailed body code of triggers in your schema?

Tandai untuk Ditinjau

(1) Point

USER_OBJECTS

USER_TRIGGER

USER_TRIGGERS (*)

None of these; you cannot view the code of the trigger body after the trigger has been created.

USER_SOURCE

Incorrect. Refer to Section 13 Lesson 5.

---

10. There are five employees in department 50. A statement trigger is created by:

CREATE OR REPLACE TRIGGER emp_upd_trigg
AFTER DELETE ON EMPLOYEES
BEGIN ...

A user now executes:
DELETE FROM employees WHERE department_id = 50;

How many times will the trigger fire, and when?

Tandai untuk Ditinjau

(1) Point

Five times, after each employee row is deleted

Once, before the DELETE is executed

Once, after the DELETE is executed (*)

11. We want to prevent employees from being deleted on Sundays. To do this, we create the following trigger:

CREATE OR REPLACE TRIGGER stop_del_emps
....... DELETE ON employees -- Line A
BEGIN
    IF TO_CHAR(SYSDATE','DY') = 'SUN' THEN
       RAISE_APPLICATION_ERROR(-20101,'Invalid delete');
    END IF;
END;

Should this be a BEFORE or AFTER trigger, and why?

Tandai untuk Ditinjau

(1) Point

It should be an AFTER trigger because the Oracle Server cannot fire the trigger until it knows that the employee has been deleted.

It should be a BEFORE trigger because you cannot use RAISE_APPLICATION_ERROR with AFTER triggers.

It does not matter, either a BEFORE or an AFTER trigger could be created.

It should be a BEFORE trigger because if an AFTER trigger were created, the employee would already have been deleted by the time the trigger checks the date. (*)

Incorrect. Refer to Section 13 Lesson 2.

---

12. A DML statement trigger fires only once for each triggering DML statement, while a row trigger fires once for each row processed by the triggering statement. True or False?

Tandai untuk Ditinjau

(1) Point

True (*)

False

Correct

---

13. Which of the following statements about INSTEAD OF triggers are NOT true? (Choose two.)

Tandai untuk Ditinjau

(1) Point

They can be created on a complex view.

They can be row triggers.

They can be created on a table.(*)

They can be statement triggers.(*)

They can be created on a simple view.

Incorrect. Refer to Section 13 Lesson 3.

---

14. You decide to create the following trigger:

CREATE OR REPLACE TRIGGER empl_trigg
BEFORE UPDATE ON employees
BEGIN
       -- Line A
       RAISE_APPLICATION_ERROR('Cannot update salary');
    ELSE
       INSERT INTO log_table values (USER, SYSDATE);
    END IF;
END;

You want the trigger to prevent updates to the SALARY column, but allow updates to all other columns. What should you code at Line A?

Tandai untuk Ditinjau

(1) Point

IF UPDATE('SALARY') THEN

IF UPDATE(SALARY) THEN

IF UPDATING('SALARY') THEN (*)

IF UPDATING(SALARY) THEN

IF UPDATING SALARY THEN

Incorrect. Refer to Section 13 Lesson 3.

---

15. Whenever an employee's JOB_ID is updated, we want to insert a row into a logging table to record the employee_id and the new value of JOB_ID. We create a row trigger whose body includes the following code:

BEGIN
    INSERT INTO logging_table (emp_id, job_id)
       VALUES -- Point A
END;

At point A, which of the following will insert the correct data into the logging table? (Choose two.)

Tandai untuk Ditinjau

(1) Point

(NEW.employee_id, NEW.job_id);

(:OLD.employee_id, :NEW.job_id);(*)

(:NEW.employee_id, :OLD.job_id);

(:OLD.employee_id, :OLD.job_id);

(:NEW.employee_id, :NEW.job_id);(*)

1. While editing a document in Microsoft Word, you go to the FILE menu and SAVE your work. To do this, Microsoft Word has executed an application trigger. True or False?

Tandai untuk Ditinjau

(1) Point

True (*)

False

Correct

---

2. Which of the following are good guidelines to follow when creating a database trigger? (Choose two.)

Tandai untuk Ditinjau

(1) Point

Where possible, use a trigger to enforce a foreign key constraint.

Use triggers to override privilege checking and view other users' private tables.

Use triggers to prevent unauthorized users from SELECTing confidential data.

Do not use a trigger to replace or duplicate something which the Oracle Server does automatically.(*)

Do not create a trigger that automatically fires another trigger.(*)

Incorrect. Refer to Section 13 Lesson 1.

---

3. You can use a database trigger to prevent invalid transactions from being committed. True or False?

Tandai untuk Ditinjau

(1) Point

True (*)

False

Correct

---

4. Examine this code:

CREATE TRIGGER new_trigg
AFTER CREATE ON reserved_word
BEGIN ...

Which of the following can be used in place of reserved_word? (Choose two.)

Tandai untuk Ditinjau

(1) Point

TABLE

TABLE employees

DATABASE(*)

SCHEMA(*)

USER

Incorrect. Refer to Section 13 Lesson 4.

---

5. The database administrator wants to write a log record every time an Oracle Server error occurs in any user's session. The DBA creates the following trigger:

CREATE TRIGGER log_errs_trigg
    -- Line A
BEGIN
    INSERT INTO errlog_table VALUES (...);
END;
What should the DBA code at Line A ?

Tandai untuk Ditinjau

(1) Point

AFTER SERVER ERROR ON DATABASE

AFTER ORACLE ERROR ON SCHEMA

AFTER ERROR ON DATABASE

AFTER SERVERERROR ON SCHEMA

AFTER SERVERERROR ON DATABASE (*)

6. A trigger automatically inserts a row into a logging table every time a user's session receives this error message:
ORA-00942: table or view does not exist
What kind of trigger is this?

Tandai untuk Ditinjau

(1) Point

A database event trigger (*)

A statement trigger

A row trigger

An AFTER trigger

A DDL trigger

Correct

---

7. The OLD and NEW qualifiers can be used with statement triggers as well as row triggers. True or False?

Tandai untuk Ditinjau

(1) Point

True

False (*)

Incorrect. Refer to Section 13 Lesson 3.

---

8. In the following code:

CREATE TRIGGER mytrigg
INSTEAD OF INSERT OR UPDATE ON my_object_name
FOR EACH ROW
BEGIN ...
my_object_name can be the name of a table. True or False?

Tandai untuk Ditinjau

(1) Point

True

False (*)

Incorrect. Refer to Section 13 Lesson 3.

---

9. Whenever an employee's JOB_ID is updated, we want to insert a row into a logging table to record the employee_id and the new value of JOB_ID. We create a row trigger whose body includes the following code:

BEGIN
    INSERT INTO logging_table (emp_id, job_id)
       VALUES -- Point A
END;

At point A, which of the following will insert the correct data into the logging table? (Choose two.)

Tandai untuk Ditinjau

(1) Point

(:NEW.employee_id, :NEW.job_id);(*)

(NEW.employee_id, NEW.job_id);

(:OLD.employee_id, :OLD.job_id);

(:OLD.employee_id, :NEW.job_id);(*)

(:NEW.employee_id, :OLD.job_id);

Incorrect. Refer to Section 13 Lesson 3.

---

10. Which command would you use to see if your triggers are enabled or disabled?

Tandai untuk Ditinjau

(1) Point

SELECT trigger_name, trigger_type
FROM USER_TRIGGERS;

SELECT trigger_name, status
FROM USER_TRIGGERS; (*)

SELECT object_name, status
FROM USER_OBJECTS
WHERE object_type = 'TRIGGER';

DESCRIBE TRIGGER

11. A user creates the following trigger:

CREATE OR REPLACE TRIGGER emp_trigg
AFTER DELETE ON employees
BEGIN
    ...
END;

The user now tries to drop the EMPLOYEES table. What happens?

Tandai untuk Ditinjau

(1) Point

Both the table and the trigger are dropped. (*)

The table is dropped but the trigger is not dropped.

An error message is displayed because you cannot drop a table that is referenced by a trigger.

The table is dropped and the trigger is disabled.

Correct

---

12. By default, any user can create a DML trigger on a table in his/her schema. True or False?

Tandai untuk Ditinjau

(1) Point

True

False (*)

Incorrect. Refer to Section 13 Lesson 5.

---

13. You need to create a trigger that will fire whenever an employee's salary or job_id is updated, but not when any other column of the EMPLOYEES table is updated. Which of the following is the correct syntax to do this?

Tandai untuk Ditinjau

(1) Point

CREATE TRIGGER emp_upd_trigg
AFTER UPDATE ON employees (salary, job_id)
BEGIN ...

CREATE TRIGGER emp_upd_trigg
AFTER UPDATE OF salary, job_id ON employees
BEGIN ... (*)

CREATE TRIGGER emp_upd_trigg
AFTER UPDATE OF salary OR job_id ON employees
BEGIN ...

CREATE TRIGGER emp_upd_trigg
AFTER UPDATE OF (salary, job_id) ON employees
BEGIN ...

Incorrect. Refer to Section 13 Lesson 2.

---

14. We want to prevent employees from being deleted on Sundays. To do this, we create the following trigger:

CREATE OR REPLACE TRIGGER stop_del_emps
....... DELETE ON employees -- Line A
BEGIN
    IF TO_CHAR(SYSDATE','DY') = 'SUN' THEN
       RAISE_APPLICATION_ERROR(-20101,'Invalid delete');
    END IF;
END;

Should this be a BEFORE or AFTER trigger, and why?

Tandai untuk Ditinjau

(1) Point

It does not matter, either a BEFORE or an AFTER trigger could be created.

It should be a BEFORE trigger because you cannot use RAISE_APPLICATION_ERROR with AFTER triggers.

It should be a BEFORE trigger because if an AFTER trigger were created, the employee would already have been deleted by the time the trigger checks the date. (*)

It should be an AFTER trigger because the Oracle Server cannot fire the trigger until it knows that the employee has been deleted.

Incorrect. Refer to Section 13 Lesson 2.

---

15. What is wrong with the following code?

CREATE OR REPLACE TRIGGER loc_trigg
BEFORE DELETE ON locations
BEGIN
    RAISE_APPLICATION_ERROR(-20201,'Invalid delete');
    ROLLBACK;
END;

Tandai untuk Ditinjau

(1) Point

Nothing is wrong, this trigger will compile and execute successfully.

The last line should be:
END loc_trigg;

You cannot use RAISE_APPLICATION_ERROR inside a trigger.

You cannot use ROLLBACK inside a trigger. (*)

The second line should be:
BEFORE DELETE OF locations