Oracle PL/SQL - Autonomous Trigger Uses Native Dynamic SQL for DDL

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL SQL Statement
  4. Declaring Autonomous Function in Package

Introduction

In the following code, an autonomous trigger uses native dynamic SQL to drop a temporary table after a row is inserted into the table log.

Demo

SQL>
SQL>-- from w w  w.  ja v  a 2s  . c  o  m
SQL>
SQL> DROP TABLE temp;

Table dropped.

SQL> CREATE TABLE temp (
  2    temp_id NUMBER(6),
  3    up_date DATE
  4  );
SQL>
SQL> CREATE OR REPLACE TRIGGER drop_temp_table
  2    AFTER INSERT ON log
  3  DECLARE
  4    PRAGMA AUTONOMOUS_TRANSACTION;
  5  BEGIN
  6    EXECUTE IMMEDIATE 'DROP TABLE temp';
  7    COMMIT;
  8  END;
  9  /

SQL> -- Show how trigger works
SQL> SELECT * FROM temp;

no rows selected

SQL>
SQL> INSERT INTO log (log_id, up_date, new_sal, old_sal)
  2  VALUES (999, SYSDATE, 5000, 4500);
SQL>
SQL>
SQL> SELECT * FROM temp;

no rows selected

SQL>
SQL>

Related Topic