PL/SQL 13-4: Creating DDL and Database Event Triggers

 Database Programming with PL/SQL

13-4: Creating DDL and Database Event Triggers

Practice Activities

Vocabulary

are fired by DDL statements: CREATE, ALTER, or DROP - DDL Triggers

A table that is currently being modified by an UPDATE, DELETE, or INSERT statement, or a table that might need to be updated by the effects of a declarative DELETE CASCADE referential integrity action - Mutating Table

enables you to call a stored procedure, rather than code the PL/SQL body in the trigger itself - CALL statement

are fired by non-SQL events in the database - Database Event triggers

Try It / Solve It

1. Explain the difference between ON SCHEMA and ON DATABASE triggers.

ON DATABASE fires the trigger for DDL on all schemas in the database when ON SCHEMA fires the trigger only for DDL on objects in your own schema.

2. Imagine that the following audit table has been created in your schema:

CREATE TABLE audit_ddl

 (action VARCHAR2(20),

 who VARCHAR2(30) DEFAULT USER,

 when TIMESTAMP DEFAULT SYSTIMESTAMP);

3. State which events would fire each of the following triggers:

A. CREATE OR REPLACE TRIGGER new_tab_trig

 AFTER CREATE ON SCHEMA

BEGIN

 INSERT INTO audit_ddl(action)

VALUES( 'New object');

END;

A new object created in my schema will fire this trigger. (CREATE)

B. CREATE OR REPLACE TRIGGER drop_tab_trig

 AFTER DROP ON SCHEMA

BEGIN

 INSERT INTO audit_ddl(action)

 VALUES( 'Deleted object');

END;

Any object deleted in my schema will fire this trigger (DROP)

C. CREATE OR REPLACE TRIGGER alter_tab_trig

 AFTER ALTER ON SCHEMA

BEGIN

 INSERT INTO audit_ddl(action)

VALUES( 'Modified object');

END;

Any object modified in my schema will fire this trigger (ALTER)

4. How could you create a trigger that executes a stored procedure and does nothing else? How

does this trigger code end differently from a normal trigger?

CREATE [OR REPLACE] TRIGGER trigger_name

timing

event

ON table_name

[REFERENCING]

[FOR EACH ROW]

[WHEN condition]

 CALL procedure_name


This end differently from a normal trigger because there is no END; statement and no semicolon at the end of the call statement

5. Create a row trigger that displays the maximum salary in the EMPLOYEES table and is fired

immediately before an employee’s salary is updated.

CREATE OR REPLACE TRIGGER max_sal

FOR UPDATE OF salary ON employees

COMPOUND TRIGGER

 max_sal NUMBER(10);

BEFORE EACH ROW IS

BEGIN

   SELECT MAX(salary) INTO max_sal

   FROM employees;

  DBMS_OUTPUT.PUT_LINE(max_sal);

END BEFORE EACH ROW;

END max_sal;

6. Test your trigger by attempting to update the salary of employee_id 100 to a new value of 25000.

What happens and why?

UPDATE employees

SET salary = 25000

WHERE employee_id = 100;


ORA-04091: table RO_A851_SQL_S02.EMPLOYEES is mutating, trigger/function may not see it

ORA-06512: at "RO_A851_SQL_S02.MAX_SAL", line 5

ORA-04088: error during execution of trigger 'RO_A851_SQL_S02.MAX_SAL'



3. WHERE employee_id = 100;


There is an error because you can't acces the data while it is modifying.

Post a Comment

0 Comments