Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2151
  • Last Modified:

Firebird create a context variable

I have a trigger for saving the changes into a history table.

For DELETE and UPDATE i use the context variable "old", and for INSERT i have to use the "new" context variable.

I want to use only ONE trigger for INSERT, UPDATE and DELETE because its easier to mantain it later, and my problem ist that i look for a way to use only one variable for acessing the values from "old" and "new" context variable. Also, if INSERT then i have to use the values from "new" else from "old". But i dondt want to use the both on the same procedure.

Hou can i create a variable and assing the "new" if INSERTING and otherwise the "old" context variable?

0
Daniel Junges
Asked:
Daniel Junges
  • 3
  • 2
  • 2
  • +1
1 Solution
 
Gary BenadeCommented:
CREATE TRIGGER TEST_ FOR TEST ACTIVE AFTER INSERT OR UPDATE OR DELETE POSITION 0 AS
DECLARE VARIABLE CONTEXTVAR VARCHAR(10);
begin
     IF (INSERTING) THEN
         CONTEXTVAR = OLD.LINK;
     ELSE IF (UPDATING) THEN
         CONTEXTVAR = OLD.LINK;
     ELSE // (DELETING)
        CONTEXTVAR = OLD.LINK;
     // REST OF CODE
end
0
 
NickUpsonSenior Network EngineerCommented:
small correction

CREATE TRIGGER TEST_ FOR TEST ACTIVE AFTER INSERT OR UPDATE OR DELETE POSITION 0 AS
DECLARE VARIABLE CONTEXTVAR VARCHAR(10);
begin
     IF (INSERTING) THEN
         CONTEXTVAR = NEW.LINK;             <-------
     ELSE IF (UPDATING) THEN
         CONTEXTVAR = OLD.LINK;             could be either, depending upon which you want to use
     ELSE // (DELETING)
        CONTEXTVAR = OLD.LINK;
     // REST OF CODE
end

I don't think firebird 2 will allow references to invalid context variables for the trigger defn, If I'm right the above will not be allowed in any trigger as eg, a delete trigger doesn't allow code that refers to a new.field
0
 
Daniel JungesAuthor Commented:
this solution i have also applied, but i have a lot of Fields. What im searching is a solution like this:

CREATE TRIGGER ....
DECLARE VARIABLE myRow ROW;
begin
     IF (INSERTING) THEN
         myRow = NEW;
     ELSE IF (UPDATING) THEN
         myRow = NEW;
     ELSE
        myRow = OLD;
    ...
    myRow.CompanyName
    ...
end

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
NickUpsonSenior Network EngineerCommented:
you could do it with execute statement

str = 'name = ' || myRow || '.CompanyName';
execute statement str;
0
 
Gary BenadeCommented:
I understand what you are trying to do but since there are no object or array datatypes available to declare in PSQL it isn't possible. Nickupsons last suggestion is the only alternative and it will work but I'm not sure it will achieve your goal of writing simpler code.
0
 
NickUpsonSenior Network EngineerCommented:
I disagree with the Question being deleted, the Question has been answered. It is, perhaps, not the answer that the OP wanted to hear.
0
 
Vee_ModCommented:
Force accepted.
Vee_Mod
Experts Exchange Moderator
0
 
Daniel JungesAuthor Commented:
i not have received the solution for my problem, so i want to delete this question and have no reason to accept one of these sugestions.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now