How do I update multiple rows in a table after it has changed?

4
  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 client BLOQUEIO='S' , other clients on the same network will be blocked:

UPDATE PCCLIENT SET BLOQUEIO='S' where codcliprinc=:OLD.CODCLIPRINC;
    
asked by anonymous 16.06.2015 / 19:34

2 answers

0

Well, it's clear that an infinite loop is rolling, see.

There is a trigger that in UPDATE of field BLOQUEIO to S , it UPDATE in field BLOQUEIO also to S .

I do not like TRIGGER. It is a feature that is not clear in the implementation flow of the application. So my first recommendation is to do a check on the application side where, if you lock one user, then it blocks all others from the same network.

But giving a second suggestion to Trigger, 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;
    
05.05.2016 / 21:05
1

You will have to trigger the 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 insert into 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 the 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 make an update on a test specific client

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

I make the select to check (see that you have changed all clients on network 2)

SELECT * FROM CLIENTE

I hope to help

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

    
25.02.2016 / 20:20