How to drop all triggers in a Firebird 1.5 database

Question

Hi

For debug purposes I need to send 1 table of an existing Firebird 1.5 database to someone.

In stead of sending the whole db , I want to send just the db with just this table - no triggers, no constraints. I can't copy the data to another db because it's just that that we want to check - why this one table is given troubles.

I am just wondering if there is a way to drop all triggers , all constraints and all but one table (using some clever trick with the system tables or so ) ?

Answer 1

If your database backup can switch to Firebird 2.1 there is some switch in gbak and isql.

Some Firebird command-line tools have been supplied with new switches to suppress the automatic firing of database triggers:

gbak -nodbtriggers
isql -nodbtriggers
nbackup -T

These switches can only be used by the database owner and SYSDBA.

Answer 2

You can drop all triggers by directly deleting them from the system table, like so:

delete from rdb$triggers
    where (rdb$system_flag = 0 or rdb$system_flag is null);

Note that the normal way of using drop trigger is certainly preferable, but it can be done.

You can also drop constraints by executing DDL statements, but to enumerate constraints and drop them in a SQL script you would need the execute block functionality that Firebird 1.5 doesn't have.

There are similar statements to delete other database objects, but actually running these successfully may be much more difficult because of dependencies between objects. You can't drop any object as long as another object depends on it. This can become really tricky due to circular references, where two (or even more) objects depend on one another, forming a cycle, so there isn't a single one that may be dropped first.

The way around this is to break one of the dependencies. A procedure for example that has dependencies to other objects can be altered to have an empty body, after which it does no longer depend on those other objects, so they may be dropped then. Dropping foreign keys is another way of eliminating dependencies between tables.

I don't know of any tool implementing such a partial delete of database objects, your use case is IMO far from common. You could however have a look at the FlameRobin source code which has a certain amount of dependency detection in the code that is used to create DDL scripts or modification statements for database objects. Armed with that information you could write your own tool to do it.

If it's a one time thing it may be enough to do this manually, though. Use any Firebird management tool of your choice for that.

Answer 3

Using GUI tool (I personally prefer IBExpert) execute following command:

select 'DROP TRIGGER ' || rdb$trigger_name || ';' from rdb$triggers
  where (rdb$system_flag = 0 or rdb$system_flag is null)

Copy result into clipboard then paste and execute within script executive window.