CODEWITHSUNDEEP
sqlfirebirdfirebird2.5
migow
migow

Reputation: 25

Create trigger to UPPER() Firebird data

I have a query to UPDATE or INSERT data in my Firebird, but this values come from what user type, so I want to create a pattern, all data as Upper.

It's possible do it in Firebird 2.5?

My #1 test

"UPDATE OR INSERT INTO ENTIDADE_001 (CODCLI,NAME) VALUES UPPER('$erp_codcli','$erp_codcli')"

My #2 test

"UPDATE OR INSERT INTO ENTIDADE_001 (CODCLI,NAME) VALUES UPPER(('$erp_codcli','$erp_codcli'))"

Upvotes: 1

Views: 638

Answers (1)

Mark Rotteveel
Mark Rotteveel

Reputation: 109015

The problem with your update or insert statements is that they are syntactically incorrect. The VALUES clause takes a value list, and UPPER(...) is not a value list, see the syntax of UPDATE OR INSERT:

UPDATE OR INSERT INTO
   {target} [(<column_list>)]
   VALUES (<value_list>)
   [MATCHING (<column_list>)]
   [RETURNING <values> [INTO <variables>]]

<column_list>      ::=  colname  [, colname  ...]
<value_list>       ::=  value    [, value    ...]
<ret_values>       ::=  ret_value    [, ret_value    ...]
<variables>        ::=  :varname [, :varname ...]

Secondly, UPPER is a function that takes a single argument.

The syntactically correct version of your statement is:

UPDATE OR INSERT INTO ENTIDADE_001 (CODCLI,NAME) 
  VALUES (UPPER('$erp_codcli'), UPPER('$erp_codcli'))

However, as Arioch 'The also said in the comments, string interpolation like this is unsafe and makes your code vulnerable to SQL injection. You should use a parameterized prepared statement, and set the values to the parameters instead.

As to the question asked in the title of your question how to create a trigger, see the Firebird documentation on triggers. A simple example of a trigger that does what you want would be:

create trigger ENTIDADE_001_UPPERCASE 
  active before insert or update on ENTIDADE_001
as
begin
  new.CODCLI = upper(new.CODCLI);
  new.NAME = upper(new.NAME);
end

See this dbfiddle for a working example.

Upvotes: 2

Related Questions