1) Create database objects with dependencies

create table t1 (c1 integer, c2 integer)

create table t2 (c1 integer)

create trigger t1_after_insert
after insert on t1
begin
 declare v integer;
 declare stmt statement;
 set v = newrow.c1;
 prepare stmt from 'insert into t2 set c1 = ?';
 execute stmt using v;
 unprepare stmt;
end

create view view_t1
as select c1, c2 from t1

create procedure sp()
begin
 declare cur cursor with return for stmt;
 prepare stmt from 'select c1, c2 from view_t1';
 open cur;
end

2) Delete column t1.c2

alter table t1 drop column c2

The statement should fail with an error, since the column t1.c2 is
referenced by the view view_t1.

3) Delete table t2

drop table t2

The statement should fail with an error, since the table t2 is referenced by
the trigger t1_after_insert.

4) Delete table t1

drop table t1

The statement should fail with an error, since the table t1 is referenced by
the view view_t1.

5) Delete view view_t1

drop view view_t1

The statement should fail with an error, since the view view_t1 is
referenced by the procedure sp.

Ole Willy Tuv

Ole,

<<  Create database objects with dependencies >>

Any references to objects in views, procedures, or functions are not bound
to the catalog.  Therefore, they can be dropped, altered, etc. without any
issues.   That's why we use dynamic SQL in the procedures and functions.
This gives the developer the flexibility to change objects around and put
them back without having to drop and re-create all of the views, functions,
and procedures that reference the objects.

If you check out other databases, they all allow for something similar.  MS
SQL Server allows you to specify whether objects such as SPs should be
schema-bound or not (WITH SCHEMABINDING).

--
Tim Young
Elevate Software
www.elevatesoft.com