How to create this process

Asked

Viewed 223 times

0

I have these two tables in the bank

cadcha (Cadastro de chamadas)

inserir a descrição da imagem aqui

cadram (Cadastro de ramal)

inserir a descrição da imagem aqui

All calls made in a particular company will be saved in this table cadcha. The destination extension field, is the extension that answered or performed this respective call. I also have a second table, called cadram, where the branch register is performed manually, and the system reports are generated from this table cadram. I would like to implement a protocol that when triggered, registers the extensions found in the field richness table cadcha on the table cadram (Obviously the extensions that have not yet registered). Example: We have there in the table cadcha the extension 4554, But in the cadram table we don’t have this extension yet. I would like the past to realize this.

What I got so far:

SELECT returns all links extensions that have not yet been registered:

SELECT DISTINCT (RAMALDESTINO)
FROM CADCHA
WHERE RAMALDESTINO 
NOT IN 
(SELECT RAMCOD FROM CADRAM)

The scope of the trial, which is the part where I couldn’t go through with it: Note: In Firebird procedures it is mandatory to pass a value per parameter and return something?

CREATE PROCEDURE SP_CADRAM_AUTOMATICO
(
teste VARCHAR(1)
) 
RETURNS 
(
    EXCEPTION "Procedure Acionada"
)
AS 
BEGIN
    FOR
        SELECT DISTINCT (RAMALDESTINO)
        FROM CADCHA
        WHERE RAMALDESTINO 
        NOT IN 
        (SELECT RAMCOD FROM CADRAM)
    DO  
    BEGIN   
        INSERT INTO CADCHA (RAMCOD, RAMDES) VALUES (RAMALDESTINO, 'RAMAL');
    END
END

Reinforcing: I need the trial to register the extensions in the table automatically for me, using as a basis the table of links (cadcha) in the field ramaldestino.

sql firebird procedure

  • Make a script to currently pollute the cadcha, and then it would be better to have a TRIGGER AFTER INSERT linked to the cadcha table.

  • The problem with Rigger is that it would slow down the system, as some customers make several calls per minute that are charged in real time on the cadcha table. So I opted for the Precedent, but I realized that her syntax is different from Rigger, which does not happen in some.

  • I understand, but then it wouldn’t be "automatic", as you think of performing this procedure?

  • The automatic I say would be on the part of not having to register the extensions manually one by one. The idea is to have a "Update extensions" button that will call this application.

1 answer

3


to procedure to do what you wish would look like this:

create procedure SP_CADRAM_AUTOMATICO
as
declare variable VRAMAL_DESTINO integer;
begin
  for
      select distinct
          (RAMALDESTINO)
      from
          CADCHA
      where
          RAMALDESTINO not in (select
                                   RAMCOD
                               from
                                   CADRAM)
      into
          VRAMAL_DESTINO
  do
  begin
    insert into CADRAM (
        RAMCOD,
        RAMDES)
    values (
        VRAMAL_DESTINO,
        'RAMAL');
  end
end

Browser other questions tagged sql firebird procedure

You are not signed in. Login or sign up in order to post.