INSTEAD OF (Deleted table)

Question

I’m having trouble creating this Rigger:

CREATE TRIGGER TGmonitoraClientes ON TB_CLIENTES INSTEAD OF DELETE
AS
BEGIN
  UPDATE TB_CLIENTES SET clienteAtivo = 0 FROM TB_CLIENTES WHERE CODcliente = deleted.CODcliente

        INSERT INTO LogClientes VALUES (deleted)

END

Message 4104, Level 16, Status 1, Tgmonitoraclients Procedure, Line 84 Multi-part "Deleted.Codclient" identifier could not be associated.

I’m not getting to use the Deleted table, any idea?

Answer 1

A procedure Trigger may be triggered even if no modification has occurred (in this case, line deletion). Therefore, it is recommended that at the beginning of the code, the number of rows in the virtual tables INSERTED (and/or DELETED) be checked. In the case of this procedure, one can have

IF (SELECT Count(*) from (SELECT top (1) * from DELETED) as D) = 0
  return;

In the UPDATE statement it was missing to list the TB_CLIENTES and DELETED tables; this was the reason for the error. The WHERE clause, in this case, is not necessary.

Here is the suggestion of the code:

-- código #1
CREATE TRIGGER TGmonitoraClientes ON TB_CLIENTES 
   INSTEAD OF DELETE
AS
BEGIN

IF (SELECT Count(*) from (SELECT top (1) * from DELETED) as D) = 0
  return;

UPDATE C
  set clienteAtivo= 0 
  from TB_CLIENTES as C
       inner join DELETED as D on C.CODcliente = D.CODcliente;

INSERT INTO LogClientes (...)
   SELECT ... from DELETED;

END;

The last part, of INSERT, needs to be detailed with the specification of the columns. Missing add error handling to procedure code Trigger.