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.]data-type-identifier
- 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:
- An SQL object for which functions, procedures, and triggers are in effect that use an abstract data type (all subtypes of the highest-level data type) that has the same inheritance relationship as the abstract data type to be deleted
- An SQL object for which functions, procedures, and triggers are in effect that use the same functions and procedures as those defined in the abstract data type to be deleted
- An SQL object for which functions, procedures, and triggers are in effect that use a function having the same owner, routine identifier, or number of parameters as the function that was defined in the abstract data type to be deleted
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:
- An SQL object for which functions, procedures, and triggers are in effect that use an abstract data type that has the same inheritance relationship as the abstract data type to be deleted
- An SQL object for which functions, procedures, and triggers are in effect that use the functions and procedures that were defined in the abstract data type to be deleted
- An SQL object for which functions, procedures, and triggers are in effect that use a function having the same owner, routine identifier, and number of parameters as the function defined in the abstract data type to be deleted
Common rules
- 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.
- If a function defined in the abstract data type definition is used in a view definition, that abstract data type cannot be deleted.
- Of the functions invalidated by the WITH PROGRAM specification, if a function that meets either of the following conditions is used in the view definition, the abstract data type cannot be deleted:
- Abstract data type is specified in the data type of an argument.
- Abstract data type is specified in the data type of a return value.
- If an SQL object being executed is invalidated by deleting the abstract data type, DROP DATA TYPE cannot be executed from within a Java procedure.
Notes
- The DROP DATA TYPE statement cannot be specified from an X/Open-compliant UAP running under OLTP.
- If the specified abstract data type contains an abstract data type function, its plug-in information will also be deleted.
- 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.
- 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.
- 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 re-create 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 re-create 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:
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.
- To access a view table that uses a function invalidated by specifying WITH PROGRAM, you must execute ALTER ROUTINE to re-create the SQL object of the function.
Example
Delete the SGML type defined by the user with authorization identifier USER1:
DROP DATA TYPE USER1.SGML