How to deactivate triggers?


You can use these SQL commands:

ALTER TRIGGER trigger_name INACTIVE;
ALTER TRIGGER trigger_name ACTIVE;

Most tools have options to activate and deactivate all triggers for a table. For example, in FlameRobin, open the properties screen for a table, click on Triggers at top and then Activate or Deactivate All Triggers options at the bottom of the page.

If you have complex triggers running in production and wish to do some bulk load while others are using the system, it is not practical to disable the triggers completely. In this case you can use various tricks to make sure triggers only fire for certain users. For example, you can create a user BULKLOAD and log in as such user when performing bulk load operation. In all the triggers you can add a code that checks the user:

if (current_user = 'BULKLOAD') then ...skip stuff...

However, this might not be suitable for any system, because you might have multiple users doing bulk load and you don't want everyone to have access to the same account. Another approach to this would be to use ROLES. Create a role named BULKLOAD and assign this role to various users that need such privilege. When such users log in, they can specify this role, work in the application and trigger will not act. The trigger code would contain something like:

if (current_role = 'BULKLOAD') then ...skip stuff...

This approach can also pose problems, because user needs to log out and log back in with a different role, and you might also limit the role use for other parts of application where user does not bulk load the data and having trigger code disabled would work against the business logic.

To work around that you can use context variables. You can tie a context variable to a transaction. For example, when you start a transaction, first execute a block to set the bulk_load flag:

execute block as begin
rdb$set_context('USER_TRANSACTION', 'bulkload', '1');
end

The trigger code should be changed to check for this flag:

CREATE TRIGGER ...
...
if (rdb$get_context('USER_TRANSACTION', 'bulkload') = '1') then exit;
...
END

This way the entire transaction is run protected from triggers, while triggers still work for all other transactions in the system.


Do you find this FAQ incorrect or incomplete? Please e-mail us what needs to be changed. To ensure quality, each change is checked by our editors (and often tested on live Firebird databases), before it enters the main FAQ database. If you desire so, the changes will be credited to your name. To learn more, visit our add content page.

If you are a commercial tool maker and your tool features a great way to handle the issue written about in this FAQ, please check out our advertisement page.



All contents are copyright © 2007-2016 FirebirdFAQ.org unless otherwise stated in the text.


Links   Firebird   News   FlameRobin   Home Inventory powered by FB  
Add content   Advertise   About  

Categories
 Newbies
 SQL
 Installation and setup
 Backup and restore
 Performance
 Security
 Connectivity and API
 HOWTOs
 Errors and error codes
 Miscellaneous