CODEWITHSUNDEEP
firebirdfirebird-3.0
Zon Ch
Zon Ch

Reputation: 21

How to get value from table using Firebird trigger

I want to get a value from the table and compare it with inserted value in the Firebird trigger. Here is my code.

SET TERM ^;

CREATE TRIGGER after_in_systab FOR SYSTEMTAB
ACTIVE AFTER INSERT POSITION 0
AS

declare sys_code integer;
select sys_code from system_table;

BEGIN
    /* enter trigger code here */ 
    if(sys_code == NEW.SYSTEM_CODE) then
    insert into logs(log_detail)values('code matched');
    end
    
END^

SET TERM;^

Upvotes: 0

Views: 796

Answers (2)

Arioch 'The
Arioch 'The

Reputation: 16045

Alternatively, you can use a singular select expression instead.

CREATE TRIGGER after_in_systab FOR SYSTEMTAB
ACTIVE AFTER INSERT POSITION 0
AS
  declare sys_code integer;
BEGIN
  sys_code = (select sys_code from system_table);
  if(sys_code == NEW.SYSTEM_CODE) then
  begin
    insert into logs(log_detail)values('code matched');
  end
END

If your select returns...

  • one single row or more, then it is the same as Mark's answer (error when multiple rows).
  • not a single row, the expression would return NULL while Mark's statement would do nothing (no change of variable value)

You may also think SQL SINGULAR existence predicate and about how it is different from EXISTS one.

You also have to make your mind clearly what should happen if the transaction was rolled back (because of any database or network error, or because an application commanded to ROLLBACK changes): should your LOG still contain a record about the data modification that was not persisted or should the LOG record vanish with the un-inserted data row it describes.

If former is the case you have to insert log records in autonomous transaction (chapter 7.6.16).

Upvotes: 1

Mark Rotteveel
Mark Rotteveel

Reputation: 109015

You need to use the INTO clause:

CREATE TRIGGER after_in_systab FOR SYSTEMTAB
ACTIVE AFTER INSERT POSITION 0
AS
  declare sys_code integer;
BEGIN
  select sys_code from system_table into sys_code;
  if(sys_code == NEW.SYSTEM_CODE) then
  begin
    insert into logs(log_detail)values('code matched');
  end
END

Upvotes: 0

Related Questions