kuis 13,oracleacademy 2019

1. There are five employees in department 50. The following trigger is created:
CREATE TRIGGER upd_emp
AFTER UPDATE ON employees
BEGIN
    INSERT INTO audit_table VALUES (USER, SYSDATE);
END;

A user now executes:

UPDATE employees
SET salary = salary * 1.1
    WHERE department_id = 50;

How many rows will be inserted into audit_table?

 Mark for Review
(1) Points


One (*)


Two


Five


Six


None of these.



Correct Correct


2. 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;
 Mark for Review
(1) Points


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


You cannot use RAISE_APPLICATION_ERROR inside a trigger.


The second line should be:
BEFORE DELETE OF locations


The last line should be:
END loc_trigg;


You cannot use ROLLBACK inside a trigger.
(*)




Correct Correct


3. 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;
 Mark for Review
(1) Points


True


False (*)



Correct Correct


4. You have created several DML triggers which reference your DEPARTMENTS table. Now you want to disable all of them using a single SQL statement. Which command should you use? Mark for Review
(1) Points


ALTER TABLE departments DISABLE TRIGGERS;


DROP ALL TRIGGERS ON departments;


ALTER TABLE departments DISABLE ALL TRIGGERS; (*)


ALTER TRIGGER DISABLE ALL ON departments;



Correct Correct


5. By default, any user can create a DML trigger on a table in his/her schema. True or False? Mark for Review
(1) Points


True


False (*)

6. 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?

 Mark for Review
(1) Points


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


The trigger is dropped but the table is not dropped.


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


The table is dropped and the trigger is disabled.


None of these.



Correct Correct


7. Which of the following best describes a database trigger? Mark for Review
(1) Points


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


A subprogram that is invoked explicitly by the calling application


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


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



Correct Correct


8. What type of database object would you create to write an auditing record automatically every time a user connects to the database? Mark for Review
(1) Points


A trigger (*)


A package


A procedure


A function


A complex view



Correct Correct


9. Which of the following are NOT allowed within a database trigger? (Choose two) Mark for Review
(1) Points

(Choose all correct answers)


COMMIT (*)


INSERT


A Boolean variable


SAVEPOINT (*)


A call to a packaged procedure



Correct Correct


10. What is the purpose of using the CALL statement in a trigger? Mark for Review
(1) Points


It allows the trigger body code to be placed in a separate procedure. (*)


It allows the trigger body code to be placed in a separate trigger.


It allows both DML events and DDL events to be handled using a single trigger.


It allows an INSTEAD OF trigger to be a statement trigger.


It prevents cascading triggers.



Correct Correct

11. You can create a trigger which prevents DDL statements on an individual table, while still allowing DDL on other tables in the same schema. True or False? Mark for Review
(1) Points


True


False (*)



Correct Correct


12. The database administrator creates a trigger that automatically disconnects user HACKER whenever HACKER connects to the database. What type of trigger is this? Mark for Review
(1) Points


A Database Event trigger (*)


A DDL trigger


A DML trigger


An INSTEAD OF trigger


A statement trigger



Correct Correct


13. Which of the following best describes conditional predicates in a trigger? Mark for Review
(1) Points


They allow the trigger code to see what data values are being inserted into a row.


They are automatically declared boolean variables which allow the trigger body to detect which DML operation is being executed. (*)


They are special cursor attributes, like %ROWCOUNT and %NOTFOUND


They are special variables which must be DECLAREd within the trigger.



Correct Correct


14. What is the event that will cause the trigger on the emp_details view below to fire?
CREATE OR REPLACE TRIGGER new_emp_dept
INSTEAD OF INSERT ON emp_details
BEGIN
  INSERT INTO new_emps
    VALUES (:NEW.employee_id, :NEW.last_name,
      UPDATE new_depts         :NEW.salary, :NEW.department_id);
      new_depts
      SET dept_sal = dept_sal + :NEW.salary
      WHERE department_id = :NEW.department_id;
END;
 Mark for Review
(1) Points


An attempt to update salary column on the new_depts table


An attempt to add a row in the emp_details view (*)


A new employee is added to the emp_details table


A procedure calls the new_emp_dept trigger.


An attempt to add a row in the new_depts table.



Correct Correct


15. What are the components of a compound trigger? Mark for Review
(1) Points


Declaration section and at least two timing sections.


Declaration section and all four timing sections.


Declaration section and at least one timing section. (*)


Declaration section, timing sections, and exception section.


Declaration section, referencing section, and timing sections.



Correct Correct





Correct Correct