16-1. | What is a trigger? |
16-2. | For which of the following events can you create a trigger? An INSERT, UPDATE, or DELETE statement on a specific object An execution of a specific procedure A user logon (or logoff) A DDL statement, such as DROP or ALTER, on a specific object
|
16-3. | What is one of the main differences between the execution of a trigger and the execution of a stored procedure? |
16-4. | What are the two modes a trigger can have? |
16-5. | Which of the following terms describes the situation in which the execution of one trigger results in the execution of another, or possibly more, different triggers? Trigger torrent Cascading triggers Chain reaction Interlock Recursive nesting
|
16-6. | What is the difference between a statement-level trigger and a row-level trigger? |
16-7. | What clause makes a trigger fire only when a specific condition is true? |
16-8. | Which of the following triggers populates the employee_id column of the employee table with the next employee_seq sequence value? CREATE OR REPLACE TRIGGER employee_ins_t1
BEFORE INSERT
ON employee
FOR EACH ROW
BEGIN
INSERT INTO employee (employee_id)
VALUES (employee_seq.nextval);
END;CREATE OR REPLACE TRIGGER employee_ins_t1
BEFORE INSERT
ON employee
FOR EACH ROW
BEGIN
SELECT employee_seq.nextval
INTO :new.employee_id
FROM dual;
END;
|
16-9. | Which of these special “pseudo” records are allowed inside a trigger? :NEW
:CURRENT
:OLDEST
:PARENT
:OLD
:NEWEST
:RECORD
|
16-10. | What system privileges are required to create a trigger? |
16-11. | What is the difference between the ALTER ANY TRIGGER privilege and the CREATE ANY TRIGGER privilege? |
16-12. | Why does the following trigger generate a “ORA-00920: invalid relational operator” error? CREATE OR REPLACE TRIGGER emp_before_ins_t
BEFORE INSERT
ON employee
FOR EACH ROW
WHEN (:NEW.mgr is null)
BEGIN
IF (:NEW.sal > 800)
THEN
:NEW.sal := 850;
END IF;
END; |
16-13. | How can you view a trigger’s compilation errors? |
16-14. | How many different ways can you recompile a trigger? |
16-15. | Why might you want to omit the OR REPLACE clause when you first create a trigger? |
16-16. | Which of the following statements are correct, and which are incorrect? A user can create a trigger in any database schema if the user has the CREATE ANY TRIGGER privilege. A user can create a trigger in any database schema (with the exception of SYS) if the user has the CREATE ANY TRIGGER privilege. Triggers can trap only DML events such as INSERT, DELETE, and UPDATE.
|
16-17. | True or false? You can define a trigger for: Any schema-level object A table A view Any nested table The entire database or a user schema
|
16-18. | Examine the following trigger: CREATE OR REPLACE TRIGGER upd_employee_commision
FOR EACH ROW
BEGIN
<<Trigger logic>>
END; Which of the following statements must you add to the trigger definition to make sure this trigger executes only after updating the comm column of the emp table? AFTER UPDATE(comm) ON emp
AFTER UPDATE ON emp
AFTER UPDATE OF comm ON emp
AFTER comm UPDATE ON emp
|
16-19. | Examine the following trigger: CREATE OR REPLACE TRIGGER insert_employee
AFTER INSERT ON emp
BEGIN
<<Trigger logic>>
END; Which of the following statements must you add to the trigger definition to make sure it executes only once for each INSERT operation on the emp table? FOR EVERY ROW
WHEN (new.sal IS NULL)
FOR EACH ROW
No modifications are necessary
|
16-20. | Why does the following trigger fails when it’s executed? CREATE OR REPLACE TRIGGER ins_emp_summary
AFTER INSERT
ON emp
BEGIN
INSERT INTO emp_summary (empno, period, ytd_salary)
VALUES (:new.empno, SYSDATE, :new.sal);
END; |
16-21. | What is a mutating table? |
/* Filename on web page: iris_emp_trig */
CREATE OR REPLACE TRIGGER ins_emp
AFTER INSERT
ON emp
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
vsal NUMBER;
BEGIN
SELECT SUM (sal)
INTO vsal
FROM emp e
WHERE e.deptno = :new.deptno;
BEGIN
INSERT INTO dept_history
VALUES (:new.deptno, vsal);
EXCEPTION
WHEN DUP_VAL_ON_INDEX
THEN
UPDATE dept_history
SET sal = vsal
WHERE deptno = :new.deptno;
END;
COMMIT;
END;