How do I update multiple rows of a table after it undergoes a certain change?

Question

  CREATE or REPLACE TRIGGER TG_ATUALIZA_BLOQUEIO AFTER UPDATE ON pcclient
  FOR EACH ROW
  begin
  if :NEW.BLOQUEIO = 'S' THEN
  UPDATE PCCLIENT SET BLOQUEIO='S' where codcliprinc=:OLD.CODCLIPRINC;
  END IF;
  end TG_ATUALIZA_BLOQUEIO;

Explaining:

After blocking a customer BLOQUEIO='S' other clients of the same network will be blocked:

UPDATE PCCLIENT SET BLOQUEIO='S' where codcliprinc=:OLD.CODCLIPRINC;

Answer 1

You will have to trigger Trigger whenever there is a change in the table.

Below is an example in SQL:

--criei a tabela cliente
CREATE TABLE CLIENTE (
ID INT PRIMARY KEY IDENTITY,
NOME VARCHAR (250),
BLOQUEADO VARCHAR (1),
REDE VARCHAR (20)
)

Below I do the Insert in the client table with NETWORK 1 and NETWORK 2

INSERT INTO CLIENTE (NOME,BLOQUEADO,REDE) VALUES
('CLIENTE1REDE1', 'N', 'REDE1'),
('CLIENTE2REDE1', 'N', 'REDE1'),
('CLIENTE3REDE1', 'N', 'REDE1'),
('CLIENTE4REDE1', 'N', 'REDE1'),
('CLIENTE5REDE1', 'N', 'REDE1'),
('CLIENTE6REDE1', 'N', 'REDE1'),
('CLIENTE7REDE1', 'N', 'REDE1'),
('CLIENTE8REDE1', 'N', 'REDE1'),
('CLIENTE9REDE2', 'N', 'REDE2'),
('CLIENTE1REDE2', 'N', 'REDE2'),
('CLIENTE2REDE2', 'N', 'REDE2'),
('CLIENTE3REDE2', 'N', 'REDE2'),
('CLIENTE4REDE2', 'N', 'REDE2'),
('CLIENTE5REDE2', 'N', 'REDE2'),
('CLIENTE6REDE2', 'N', 'REDE2'),
('CLIENTE7REDE2', 'N', 'REDE2'),
('CLIENTE8REDE2', 'N', 'REDE2'),
('CLIENTE9REDE2', 'N', 'REDE2');

Then I create Trigger

CREATE TRIGGER [dbo].[ATUALIZA_BLOQUEIO]
ON [dbo].[CLIENTE]
FOR UPDATE
AS
BEGIN

    DECLARE
    @ID INT,
    @BLOQUEADO VARCHAR (1),
    @REDE VARCHAR(20)

    SELECT @ID = ID, @REDE = REDE, @BLOQUEADO = BLOQUEADO FROM inserted

--Verifico se o valor do cliente que foi alterado esta 
--como bloqueado, caso esteja repasso para os demais
--clientes da mesma rede
    IF (@BLOQUEADO = 'S')
    BEGIN
    UPDATE CLIENTE SET BLOQUEADO = 'S' WHERE REDE = @REDE
    END
    END

I do an update on a specific test client

UPDATE CLIENTE SET BLOQUEADO = 'S' WHERE NOME = 'CLIENTE8REDE2'

I select to check (see you changed all network clients 2)

SELECT * FROM CLIENTE

I hope to help

Note: In oracle you can change the dynamics a little in the creation of Trigger, but only a little of the syntax.

Answer 2

Well, it’s clear that there’s a infinite loop, look at you.

There is a Trigger that, in the UPDATE country BLOQUEIO for S, he does UPDATE in the field BLOQUEIO also to S.

I don’t like TRIGGER. It’s a feature that isn’t clear in the application execution flow. So my first recommendation is that you run a check, on the application side, where, if you block a user, then block all the others from the same network.

But, giving Trigger a second suggestion, try the following:

CREATE or REPLACE TRIGGER TG_ATUALIZA_BLOQUEIO AFTER UPDATE ON pcclient
FOR EACH ROW
declare
   v_id NUMBER;
   v_exists NUMBER;
begin

if :NEW.BLOQUEIO = 'S' THEN

-- VERIFICA SE EXISTE OUTROS USUARIOS DA MESMA REDE AINDA NÃO BLOQUEADOS
SELECT COUNT(*) INTO v_exists FROM DUAL 
WHERE EXISTS (SELECT * FROM PCCLIENT WHERE CODCLIPRINC = :OLD.CODCLIPRINC AND BLOQUEIO <> 'S' AND ID <> :OLD.ID);

-- SE EXISTIR
IF V_EXISTS > 0 THEN
   -- PEGA O ID DE UM DESSES USUARIOS
   SELECT ID into V_ID FROM PCCLIENT WHERE CODCLIPRINC = :OLD.CODCLIPRINC AND ID <> :OLD.ID AND ROWNUM = 1;

   -- BLOQUEIA O CARA
   -- IMPORTANTE!!! AQUI IRA DISPARAR O TRIGGER NOVAMENTE, 
   -- BLOQUEANDO OS USUARIO EM CASCATA
   UPDATE PCCLIENT SET BLOQUEIO='S' where ID = V_ID;

END IF


END IF;
end TG_ATUALIZA_BLOQUEIO;