Oracle PL/SQL Programming: A Developer's Workbook

Intermediate

Q:
16-22.	Executing the procedure results in an error because DBMS_SQL treats :OLD and :NEW as ordinary bind variables; consequently, it complains that not all the binds have been assigned. In short, the :OLD and :NEW predicates can’t be directly referenced in statements executed via dynamic SQL.
Q:
16-23.	The following template shows how to raise an error when a DML statement violates a business rule: /* Filename on web page: secure_del_trigger.sql */ CREATE OR REPLACE TRIGGER secure_del_trigger BEFORE DELETE ON emp FOR EACH ROW DECLARE unauthorized_deletion EXCEPTION; BEGIN IF <your business rule is violated> THEN RAISE unauthorized_deletion; END IF; EXCEPTION WHEN unauthorized_deletion THEN raise_application_error (-20500, 'This record cannot be deleted'); END; /
Q:
16-24.	(b). Object privileges must be granted directly to the trigger owner and cannot be acquired through database roles.
Q:
16-25.	The triggers are: Invalid. The trigger explicitly calls SQL COMMIT, which is forbidden in Oracle. Invalid. The trigger dynamically creates a sequence via the DDL statement CREATE SEQUENCE that is issuing an implicit commit. SQL DDL statements are not allowed in triggers because DDL statements issue implicit COMMITs upon completion. Valid. A system trigger is the only type of trigger that allows CREATE/ALTER/DROP TABLE statements and ALTER…COMPILE in the trigger body, despite the fact that ...