DROP [PUBLIC] VIEW (Delete view table, delete public view table)

Function

DROP VIEW deletes a view table.

All users can delete these view tables (public view) without having to modify the table identifier with the authorization identifier.

(1) DROP VIEW (Delete view table)

Privileges

Owner of the view table

The owner of a view table can delete that view table.

Users with the DBA privilege

Users with the DBA privilege can delete view tables owned by other users.

Format

DROP VIEW [authorization-identifier.]table-identifier [WITH PROGRAM]

Operands

authorization-identifier
Specifies the authorization identifier of the user who owns the view table.
When this operand is omitted, the authorization identifier of the user who is executing the command is assumed.
table-identifier
Specifies the name of the view table to be deleted.
WITH PROGRAM
When deleting a view table, this operand is specified to nullify the SQL object for which functions, procedures, and triggers that use the view table are in effect.
If WITH PROGRAM is omitted, and if there is an SQL object for which functions, procedures, and triggers that use a view table are in effect, that view table cannot be deleted.

Common rules

  1. DROP VIEW deletes the access privileges for the view table as well as any view tables that are defined from the view table that is to be deleted.
  2. DROP VIEW cannot be executed from within a Java procedure if the deletion results in invalidation of the SQL object being executed.

Notes

  1. The DROP VIEW statement cannot be specified from an X/Open-compliant UAP running under OLTP.
  2. If an SQL object for which functions, procedures, or triggers are in effect is nullified by specifying WITH PROGRAM, any information associated with the nullified functions, procedures, or triggers in the SQL_ROUTINE_RESOURCES dictionary table is deleted.
  3. Before executing the SQL object associated with the function, procedure, or trigger that was nullified by specifying WITH PROGRAM, you need to re-create the function, procedure, or trigger by executing ALTER ROUTINE, ALTER PROCEDURE, or ALTER TRIGGER.
  4. To execute the SQL object for the trigger that was nullified by specifying WITH PROGRAM, you need to perform one of the following operations:
    • Redefine the view table, and re-create the trigger SQL object by executing either ALTER TRIGGER or ALTER ROUTINE.
    • Delete the nullified trigger by using DROP TRIGGER and then redefine the trigger by using CREATE TRIGGER so that the deleted view table is not used. However, if there are triggers that satisfy all of the following conditions, they should all be deleted by using DROP TRIGGER and redefined by using CREATE TRIGGER in the order in which they were defined so that the order in which trigger actions are executed does not change:
      Conditions:
      [Figure]The defined trigger is later than the nullified trigger.
      [Figure]The nullified trigger is the same as the defined table.
      [Figure]The nullified trigger is the same as the trigger event (INSERT, UPDATE, or DELETE) (for UPDATE, the nullified trigger is considered to be the same as the trigger event regardless of whether a trigger event column is specified or the contents of the specification).
      [Figure]The nullified trigger has the same trigger action timing (BEFORE or AFTER) as the trigger event.
      [Figure]The nullified trigger has the same trigger action units (units of rows or statements) as the trigger event.

Example

Delete view table VSTOCK1 defined from stock table STOCK:

DROP VIEW VSTOCK1

(2) DROP PUBLIC VIEW (Delete public view)

Privileges

Owner (definer) of a specified public view
A user can delete any public view that he or she owns (defines).
User with DBA privileges
These users can delete public views owned by other users.

Format

DROP PUBLIC VIEW table-identifier [WITH PROGRAM]

Operands

For descriptions of operands other than PUBLIC and table-identifier, see (1) DROP VIEW (Delete view table) under DROP [PUBLIC] VIEW (Delete public view, delete public view table) in this chapter.

Specify this operand when deleting a public view.

Specifies the name of the public view to be deleted.

Common rules

For details about the common rules, see (1) DROP VIEW (Delete view table) under DROP [PUBLIC] VIEW (Delete public view, delete public view table) in this chapter.

Notes

For the notes, see (1) DROP VIEW (Delete view table) under DROP [PUBLIC] VIEW (Delete public view, delete public view table) in this chapter.

Example

Delete the public view (PVSTOCK1) defined for an inventory table (STOCK).

DROP PUBLIC VIEW PVSTOCK1