This page is part of the book SQL-99 Complete, Really, by Peter Gulutzan & Trudy Pelzer. The authors have graciously allowed us to reproduce the contents of the book here. Because the book is about the SQL-99 standard, the contents of this and other pages in the book may not directly apply to MariaDB. Use the navigation bar to navigate the book.

The DROP MODULE statement destroys an SQL-server Module. The required syntax for the DROP MODULE statement is as follows.

DROP MODULE <Module name> {RESTRICT | CASCADE}

The <Module name> must identify an existing Module whose owner is either the current <AuthorizationID> or a Role that the current <AuthorizationID> may use. That is, only the <AuthorizationID> that owns the Module may drop it.

The effect of DROP MODULE <Module name> RESTRICT, e.g.:

DROP MODULE module_1 RESTRICT;

is that the Module named is destroyed, provided that the Module doesn't contain the definition of an SQL-invoked routine that is invoked outside of the Module i.e.: in an SQL routine that isn't defined in this Module or in any View definition, Trigger definition, Constraint or Assertion definition. That is, RESTRICT ensures that only a Module with no dependent Objects can be destroyed. If the Module is used by any other Object, DROP MODULE ... RESTRICT will fail.

The effect of DROP MODULE <Module name> CASCADE, e.g.:

DROP MODULE module_1 CASCADE;

is that the Module named is destroyed.

Successfully dropping a Module has a three-fold effect:

  1. The Module named is destroyed.
  2. All Privileges held on the Module by the <AuthorizationID> that owns it are revoked (by the SQL special grantor, "_SYSTEM") with a CASCADE revoke behaviour, so that all Privileges held on the Module by any other <AuthorizationID> are also revoked.
  3. All SQL routines, Triggers, Views and Constraints that depend on the Module are dropped with a CASCADE drop behaviour.

Comments

Comments loading...