How do I create an alert through a Trigger by not blocking the Insert?

Asked

Viewed 223 times

1

Guys, usually whenever I create a Rigger is to perform insertion blocking or only update values.

But this time I just need the system to alert not stopping the person performing the document download. But even though I’m commenting on Trigger’s rollback the system still hangs the insertion. Could someone please help me.

Look at my code:

ALTER TRIGGER ALERTA_CLIENTE_SEM_FOTO on PAGAMENTOS

FOR UPDATE, INSERT NOT FOR REPLICATION
AS 
    BEGIN

        IF (SELECT COUNT(*) FROM Pagamentos P 
            INNER JOIN INSERTED I ON P.CodCliente=I.CodCliente
            INNER JOIN ClientesImagens C ON P.CodCliente=C.CodCliente ) = 0



          BEGIN
            RAISERROR('O cadastro do cliente está sem foto! Adicione uma foto!',16,1)
            --ROLLBACK TRANSACTION
            END
    END
mysql database sql-server mysqli

  • What’s a lock? It locks the table, doesn’t execute the commit? You should commit the record even with Raiseerror?

  • Another thing, you’re testing to see if the Count(*) is coming null?

  • 'Lock the insertion' what I mean is that SQL is blocking to continue the operation because the client’s registration has no photo. I don’t want him to stop, I just want him to return the message and continue. Even in the customer’s registration not having photo the person can discharge the document.

  • I’ll find out what you asked for

  • You didn’t inform your SGBD correctly. It SQL Server or MySQL? It seems to me that your rule should not stand in a TRIGGER Yes in a business rule layer in the application. Anyway, one way to solve your problem is to insert your message in a table of alerts and after performing the insertion consult this table to see if any warning should be shown.

1 answer

0

The correct function that I should use is the PRINT('RETURN MESSAGE') function. This would work, SQL returns the message and proceeds with the function. But in the system that I work, the system has no treatment for the print function, only for the ROLLBACK and the rollback will always lock. I mean, in the system I work, there’s no way I can put this alert.

Browser other questions tagged mysql database sql-server mysqli

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