DROP DATA TYPE (Delete user-defined data type)

Function

DROP DATA TYPE deletes an abstract data type.

Privileges

Owners of abstract data types

A user can delete an abstract data type owned by that user.

Users with the DBA privilege

These users can delete abstract data types owned by other users.

Format

DROP DATA TYPE [authorization-identifier.] data-type-identifier
       [WITH PROGRAM]

Operands

authorization-identifier
Specifies the authorization identifier of the owner of the data type that is to be deleted.
data-type-identifier
Specifies the identifier of the data type that is to be deleted.

When deleting an abstract data type, this option is specified to nullify an SQL object for which any of the following functions, procedures, and triggers are in effect:

If WITH PROGRAM is omitted and if there is an SQL object for which any of the following functions, procedures, and triggers are in affect, the abstract data type cannot be deleted:

Common rules

  1. The specified abstract data type is not deleted if there are base tables, indexes, abstract data types, routines in an abstract data type, routines, and triggers that use the specified abstract data type.
  2. Even when WITH PROGRAM is specified, if a function defined in the abstract data type definition is used in a view definition, that abstract data type cannot be deleted.
  3. DROP DATA TYPE cannot be executed from within a Java procedure if the deletion results in invalidation of the SQL object being executed.

Notes

  1. The DROP DATA TYPE statement cannot be specified from an X/Open-compliant UAP running under OLTP.
  2. If the specified abstract data type contains an abstract data type function, its plug-in information will also be deleted.
  3. If an SQL object for which functions, procedures, or triggers are in effect is nullified by specifying WITH PROGRAM, any rows associated with the nullified functions, procedures, or triggers in the SQL_ROUTINE_RESOURCES dictionary table are deleted.
  4. 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.
  5. Before executing the SQL object associated with the trigger that was nullified by specifying WITH PROGRAM, you need to execute either ALTER TRIGGER or ALTER ROUTINE to recreate the trigger SQL object. However, to execute an SQL object associated with a trigger that was using the function and procedure that were defined in the deleted abstract data type definition, you need to perform either of the following operations:
    • Redefine the abstract data type and execute either ALTER TRIGGER or ALTER ROUTINE to recreate the trigger SQL object.
    • Delete the nullified trigger by using DROP TRIGGER, and then redefine the trigger by using CREATE TRIGGER so that the deleted functions and procedures are not reused in the deleted abstract data type definition. If triggers satisfying all of the following conditions exist, use DROP TRIGGER to delete them all, and redefine the triggers using CREATE TRIGGER in the order in which they were defined, so that there is no change in the sequence of execution of trigger actions.
      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 the SGML type defined by the user with authorization identifier USER1:

DROP DATA TYPE USER1.SGML