I'm having trouble creating this trigger:
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, State 1, Procedure TGmonitoring Clients, Line 84 The multi-part identifier "deleted.COD" could not be associated.
I'm not able to use the deleted table, any idea?
A trigger procedure can be triggered even if no modification has occurred (in this case, line deletion). It is therefore recommended that, at the beginning of the code, the number of rows in the INSERTED (and / or DELETED) virtual tables be checked. In the case of this procedure, you can have
IF (SELECT Count(*) from (SELECT top (1) * from DELETED) as D) = 0
return;
In the UPDATE statement, the tables TB_CLIENTS and DELETED were missing; that was the reason for the error. The WHERE clause, in this case, is not required.
Here is the code hint:
-- 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 by specifying the columns. procedure error code is missing.