DROP VIEW (Delete view table)
Function
DROP VIEW deletes a view table.
This command also deletes view tables (public views) that can be used by any user by means of the same table identifier without being qualified with an 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.] table-identifier [WITH PROGRAM]
- 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
- 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.
- DROP VIEW cannot be executed from within a Java procedure if the deletion results in invalidation of the SQL object being executed.
Notes
- The DROP VIEW statement cannot be specified from an X/Open-compliant UAP running under OLTP.
- 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.
- Before executing the SQL object associated with the function, procedure, or trigger that was nullified by specifying WITH PROGRAM, you need to recreate the function, procedure, or trigger by executing ALTER ROUTINE, ALTER PROCEDURE, or ALTER TRIGGER.
- 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 recreate 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:
The defined trigger is later than the nullified trigger.
The nullified trigger is the same as the defined table.
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).
The nullified trigger has the same trigger action timing (BEFORE or AFTER) as the trigger event.
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 details about operands other than PUBLIC or table-identifier, see (1) DROP VIEW.
Specify this operand when deleting a public view.
Specifies the name of the public view to be deleted.
Common rules
Notes
Example
Delete the public view (PVSTOCK1) defined for an inventory table (STOCK).
DROP PUBLIC VIEW PVSTOCK1