Using "If" inside triggers

Question

Using "If" inside triggers

Navigation

#1 by (1 votes)
#2 by (0 votes)

1

I have created a trigger to collect an entered value, relate it to another table and change it. That value can be quite peculiar, so I look for it in two fields and also concatenating it:

create or replace TRIGGER "INTER_AGENTE" 
BEFORE INSERT OR UPDATE
ON intercambios
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT ag_nombre INTO :NEW.int_vendedor 
FROM agentes 
WHERE ag_denominacion like concat(:NEW.int_vendedor,'%') 
      or :NEW.int_vendedor like concat(ag_nombre, '%');

This works correctly in all cases except one, which I get error because it finds matches in two records, in different fields.

Find a value that looks like ag_name in ag_name="CASE1", but it also finds a value that resembles it in ag_name and then introduces ag_name="CASE2".

It occurs to me to give preferences, that is, if it finds a match with ag_denomination so that it stays with that. If you do not find anything, look in ag_name.

How do you see it? Is it possible?

sql oracle plsql trigger

asked by Pablo 11.05.2016 в 14:19

source

2 answers

Search for a word in a matrix by reading from right to left Insert journal in SQL Server with data from another table in another server

score 1 · Answer 1

Try with top 1 changing this:

SELECT ag_nombre INTO :NEW.int_vendedor

for this:

SELECT top 1 ag_nombre INTO :NEW.int_vendedor

If not, I also think of it in the following way:

if(exists(SELECT top 1 ag_nombre FROM agentes WHERE ag_denominacion like concat(:NEW.int_vendedor,'%'))) 
    begin       
        SELECT ag_nombre INTO :NEW.int_vendedor 
        FROM agentes 
        WHERE ag_denominacion like concat(:NEW.int_vendedor,'%');
    end
else
    begin
        SELECT ag_nombre INTO :NEW.int_vendedor 
        FROM agentes 
        WHERE :NEW.int_vendedor like concat(ag_nombre, '%');
    end

You should validate it, I'm not sure, but that's the idea.

score 0 · Answer 2

Even if you could do it with a IF , this would imply consulting the agentes table at least 2 times (or up to 3 depending on how you do it).

Since it is a trigger that triggers for each inserted or modified record in the intercambios table, it would be best to avoid consulting the agentes table several times.

Therefore, to achieve what you ask, but without worsening the performance, I suggest you modify the query using a ORDER BY clause to return in priority the records that match ag_denominacion and then those that match ag_nombre . Then you can use rownum to limit the result to the first record:

SELECT ag_nombre INTO :NEW.int_vendedor
  FROM (SELECT ag_nombre
          FROM agentes
         WHERE ag_denominacion LIKE CONCAT(:NEW.int_vendedor,'%')
            OR :NEW.int_vendedor LIKE CONCAT(ag_nombre, '%')
         ORDER BY CASE WHEN ag_denominacion LIKE CONCAT(:NEW.int_vendedor,'%')
                       THEN 1 ELSE 2 END)
 WHERE ROWNUM = 1;