0

How to remove Firebird's triggers created automatically? I've got an error:

SQL> drop trigger CHECK_4;
Statement failed, SQLSTATE = 42000
unsuccessful metadata update
-DROP TRIGGER CHECK_4 failed
-Triggers created automatically cannot be modified

There is no "CHECK_4" in show triggers; , however when I try to work with db I've got something like that:

fdb.fbcore.DatabaseError: ('Error while executing SQL statement:\n- SQLCODE: -625\n- validation error for column "CLIENTS"."DIRECTION", value "*** null ***"\n- At trigger \'CHECK_4\'', -625, 335544347)
pilcrow
  • 56,591
  • 13
  • 94
  • 135
volkoshkursk
  • 325
  • 1
  • 2
  • 14
  • they most probably were created as the back-end of `CHECK CONSTRAINT` so determine the corresponding constraint and drop it – Arioch 'The May 20 '21 at 17:30
  • oh, you even have `for column "CLIENTS"."DIRECTION"` - so see the constraints on that column. But before you violate the rules - think that there was the resone for those rules. You have the power to destroy any data within your computer (and even the computer itself) if you really intend to do so, but maybe it is not always the wise thing to rush to do. – Arioch 'The May 20 '21 at 17:32
  • 1
    и специально для русских - https://www.sql.ru/forum/interbase - BTW IBExpert IDE is free for xUSSR users, unless you really do prefer iSQL CLI – Arioch 'The May 20 '21 at 17:33
  • 1
    The name "CHECK_4" suggests your table has a CHECK CONSTRAINT, one of a pair of automatically created triggers run BEFORE you INSERT or UPDATE the record to enforce the constraint. You need to find the internal name of your constraint (if you did not name it during CREATE TABLE) and then drop and remake it. See [_How do I alter a CHECK constraint on Firebird?_](https://stackoverflow.com/questions/27829314/how-do-i-alter-a-check-constraint-on-firebird) – pilcrow May 20 '21 at 21:50
  • 1
    This could also be a `NOT NULL` constraint, which can only be dropped since Firebird 3 using [`ALTER TABLE ... ALTER COLUMN ... DROP NOT NULL`](https://www.firebirdsql.org/file/documentation/html/en/refdocs/fblangref30/firebird-30-language-reference.html#fblangref30-ddl-tbl-alter) (or require direct system table manipulation in earlier versions). – Mark Rotteveel May 21 '21 at 07:41
  • @MarkRotteveel, perhaps! What would that constraint specification look like? In [my fiddling around](https://dbfiddle.uk/?rdbms=firebird_3.0&fiddle=e63873804105489c801229f21aa2e9c2), the error message from a NOT NULL appears quite similar, but I do not see any corresponding entries in RDB$TRIGGERS. – pilcrow May 21 '21 at 13:24
  • @pilcrow I can't recall the exact specifics, maybe my answer here is helpful: [How to find which table column corresponds to a constraint in a Firebird database?](https://stackoverflow.com/questions/25235525/how-to-find-which-table-column-corresponds-to-a-constraint-in-a-firebird-databas) – Mark Rotteveel May 21 '21 at 13:28
  • The problem was in the data type in database: it was boolean. After changing to smallint all works – volkoshkursk May 24 '21 at 11:11

0 Answers0