Firebird3- Active and InActive ALL Triggers

[Henry Olive 5]

I wish everyone a healthy and a happy new year.

 

In Interbase I used to use below codes for InActive & Active ALL triggers

But i get error msg. in FB-3.08

The Message = UPDATE operation is not allowed for system table RDB$TRIGGERS

How can i set InActive  and Active ALL Triggers in FB-3.08 ?

Thank You

 

//Active All Triggers

  SQLText:='UPDATE RDB$TRIGGERS set RDB$TRIGGER_INACTIVE = 0 '+ // 0=ACTIVE, 1=INACTIVE
           'WHERE RDB$TRIGGER_NAME IN( SELECT A.RDB$TRIGGER_NAME '+
           'FROM RDB$TRIGGERS A '+
           'LEFT JOIN RDB$CHECK_CONSTRAINTS B ON B.RDB$TRIGGER_NAME = A.RDB$TRIGGER_NAME '+
           'WHERE ((A.RDB$SYSTEM_FLAG = 0) or (A.RDB$SYSTEM_FLAG is null)) and '+
           'A.rdb$trigger_source not starting with ''CHECK'' and '+
           '(B.rdb$trigger_name is null) and (NOT(A.RDB$TRIGGER_NAME LIKE ''RDB$%'')))';

 

// InActive All Triggers

SQLText:='UPDATE RDB$TRIGGERS SET RDB$TRIGGER_INACTIVE = 1 '+ // 0=ACTIVE, 1=INACTIVE
           'WHERE RDB$TRIGGER_NAME IN( SELECT A.RDB$TRIGGER_NAME '+
           'FROM RDB$TRIGGERS A '+
           'LEFT JOIN RDB$CHECK_CONSTRAINTS B ON B.RDB$TRIGGER_NAME = A.RDB$TRIGGER_NAME '+
           'WHERE ((A.RDB$SYSTEM_FLAG = 0) or (A.RDB$SYSTEM_FLAG is null)) and '+
           'A.rdb$trigger_source not starting with ''CHECK'' and '+
           '(B.rdb$trigger_name is null) and (NOT(A.RDB$TRIGGER_NAME LIKE ''RDB$%'')))';
 

          P.s : I was running above SQL commands in Delphi and in RunTime

Edited January 2, 2022 by Henry Olive

[rvk 29]

Was this still possible in the latest Interbase version???

 

For Firebird 3+ at least, directly updating the RDB$TRIGGERS isn't allowed anymore.

See the comment here: https://gist.github.com/martinusso/1278962/07650568e3e71cb369dba68562a1e69e7fc4ba33

 

You could try the EXECUTE BLOCK mentioned there. (I didn't test this)

(Inactivating. Activating could be done similarly)

 

SET TERM ^ ;
EXECUTE BLOCK AS
DECLARE VARIABLE NOMTABLE varchar(100);
DECLARE VARIABLE REQ1 Varchar(100);
BEGIN
  for select x.RDB$TRIGGER_NAME from rdb$triggers x where
    rdb$trigger_source is not null and (coalesce(rdb$system_flag,0) = 0)
    and rdb$trigger_source not starting with 'CHECK' into :NOMTABLE
  do
  begin
    NOMTABLE=trim(NOMTABLE);
    req1= 'ALTER TRIGGER ' || :NOMTABLE || ' INACTIVE;';
    execute statement req1;
  end
END^
SET TERM ; ^

 

[Henry Olive 5]

Thank you so much RVK

I tested your code in FlameRobin it works w/o any problem

But when i try the same code in Delphi i get  "Token Unknown Line1 Column 5  TERM."   err.msg.

What am i doing wrong ?

Here below my codes

 

  SQLText := 'SET TERM ^ ;  '+
  'EXECUTE BLOCK AS '+
  'DECLARE VARIABLE NOMTABLE varchar(100); '+
  'DECLARE VARIABLE REQ1 Varchar(100); '+
  'BEGIN '+
  'for select '+

  'x.RDB$TRIGGER_NAME from rdb$triggers x where '+
  'rdb$trigger_source is not null and (coalesce(rdb$system_flag,0) = 0) '+
  'and rdb$trigger_source not starting with ''CHECK'' into :NOMTABLE '+
  'do begin '+
  'NOMTABLE=trim(NOMTABLE); '+
  'req1= ''ALTER TRIGGER '' || :NOMTABLE || '' ACTIVE;''; '+
  'execute statement req1; '+
  'end '+
  'END^ '+
  'SET TERM ; ^' ;

 

  SQLDataset1.Close;

  SQLDataset1.CommandText :=SQLText;

  SQLDataset1.ExecSql;

 

[rvk 29]

You could try to remove the SET TERM lines (top and bottom). Not sure if they are needed in commandtext. Then also remove the ^ after the last END.

 

 

 

 

[Henry Olive 5]

Thank you SO MUCH  RVK

I Removed all SET TERM and ^ in last END

This time i get Token Unknown Line 1 Column 279  ?.

Interesting your code works perfectly in Flame Robin

but doesnt work in Delphi Run Time

Line 1 Column 279 =   :NOMTABLE

Edited January 3, 2022 by Henry Olive

[Guest]

I guess you must check your DACs (Data Access Components) way of handling SQL script.

You have no crlf+s in you built string so you get "line 1 col 279", print the string and count or insert crlf's to get a "better" report.

[rvk 29]

46 minutes ago, Henry Olive said:

This time i get Token Unknown Line 1 Column 279  ?.

Maybe it's because ExecSQL with CommandText only expects one SQL command. Not an entire EXECUTE BLOCK.

 

I always use TIbScript for this (from IBX). I'm not sure what the script execute equivalent is for your db-style components (probably dbExpress seeing as you are using TSQLDataset).

 

You could also put this code in a stored procedure (activate and inactivate) and call that if you need to call it more often.

(you can call a stored procedure with ExecSQL if you set the commandtype to ctStoredProc.)

 

Edited January 3, 2022 by rvk

[Henry Olive 5]

Thank you SO SO MUCH  RVK

Sorry i'm new in Firebird.

I really appriciate the time you spend for me.

Here below my Procedure i get Token Unknown EXECUTE  error msg.

What am i doing wrong ?

 

SET TERM ^ ;

CREATE PROCEDURE TRIGGERSINACTIVE
EXECUTE BLOCK AS
DECLARE VARIABLE NOMTABLE varchar(100);
DECLARE VARIABLE REQ1 Varchar(100);
BEGIN
  for select x.RDB$TRIGGER_NAME from rdb$triggers x where
    rdb$trigger_source is not null and (coalesce(rdb$system_flag,0) = 0)
    and rdb$trigger_source not starting with 'CHECK' into :NOMTABLE
  do
  begin
    NOMTABLE=trim(NOMTABLE);
    req1= 'ALTER TRIGGER ' || :NOMTABLE || ' INACTIVE;';
    execute statement req1;
  end
END^

SET TERM ; ^
 

[rvk 29]

6 minutes ago, Henry Olive said:

Sorry i'm new in Firebird.

No problem. We've all got to start somewhere 

 

6 minutes ago, Henry Olive said:

What am i doing wrong ?

The EXECUTE BLOCK method groups multiple SQL commands together. But if you are using a STORED PROCEDURE all those commands are already grouped together by the STORED PROCEDURE itself. So you can remove that statement.

 

This should work in FlameRobin (under Object > New > Procedure when connected to the database).

SET TERM ^ ;
CREATE PROCEDURE TRIGGERSINACTIVE AS
DECLARE VARIABLE NOMTABLE varchar(100);
DECLARE VARIABLE REQ1 Varchar(100);
BEGIN
  for select x.RDB$TRIGGER_NAME from rdb$triggers x where
    rdb$trigger_source is not null and (coalesce(rdb$system_flag,0) = 0)
    and rdb$trigger_source not starting with 'CHECK' into :NOMTABLE
  do
  begin
    NOMTABLE=trim(NOMTABLE);
    req1= 'ALTER TRIGGER ' || :NOMTABLE || ' INACTIVE;';
    execute statement req1;
  end
END^
SET TERM ; ^

 

Edited January 3, 2022 by rvk

[Henry Olive 5]

Thank you SO SO SO MUCH  RVK

I created the procedures w/o any problem