How to select column name in Firebird trigger after insert

Question

I am using the Firebird trigger. I want to fetch party_name on the base of party_id after insert trigger. And insert party_name into the logs table. Here is my code.

SET TERM ^ ;
CREATE TRIGGER cash_after_insert CASH_FLOW 
AFTER INSERT 
POSITION 0 
AS
DECLARE getPartyName for
(select party_name from party where party_id=NEW.party_id);
BEGIN
insert into logs(date,username,logs)values('now',getPartyName,'added data');
END^
SET TERM ; ^

Answer 1

You're declaring a cursor, which requires additional statements (OPEN, FETCH, and CLOSE) to actually retrieve values. Instead, you can use a select expression which is a lot simpler than using an explicit cursor:

insert into logs(date,username,logs) values (
  'now', 
  (select party_name from party where party_id = NEW.party_id), 
  'added data');

As an aside, the syntax you use in your question is incorrect (or at least incomplete). For example, the create trigger statement should be CREATE TRIGGER cash_after_insert for CASH_FLOW, the cursor declaration misses the CURSOR keyword (DECLARE getPartyName cursor for ...), and date is a reserved word, so needs to be quoted ("DATE").

In other words, use:

SET TERM ^ ;
CREATE TRIGGER cash_after_insert for CASH_FLOW 
AFTER INSERT 
POSITION 0 
AS
BEGIN
insert into logs ("DATE", username, logs) values (
  'now', 
  (select party_name from party where party_id = NEW.party_id),
  'added data');
END^
SET TERM ; ^