How to create a trigger so that it does not erase a record if it contains certain characteristics in sql server

Question

How to create a trigger so that it does not erase a record if it contains certain characteristics in sql server

Navigation

#1 by (0 votes)

0

That is, create a trigger that prevents me from deleting a record from the 'person' table, but only if the name contains the following characters: 'JUAN' and print the message that can not be deleted

CREATE TRIGGER borrar
ON PERSONA
INSTEAD OF DELETE
AS
SET NOCOUNT ON
DECLARE @NOMBRE VARCHAR(30)
SELECT @NOMBRE = D.NOMBRE 
FROM PERSONA P INNER JOIN DELETED D 
ON P.ID_PER=D.ID_PER
IF (@NOMBRE LIKE '%JUAN%')
BEGIN
PRINT 'NO ES POSIBLE ELIMINAR'
END
ELSE
DELETE FROM PERSONA WHERE NOMBRE=@NOMBRE
GO

sql sql-server

asked by ara celi 16.05.2017 в 20:54

source

1 answer

error when consulting a table with name something $ something Problem in onClickListener with Button in Fragment to show data in Activity that contains the Fragment

score 0 · Accepted Answer

It's more or less like this:

CREATE TRIGGER Ejemplo ON persona
    FOR DELETE
AS 
BEGIN

    IF EXISTS (SELECT TOP 1 1 FROM DELETED WHERE CHARINDEX('JUAN', NOMBRE) > 0)
    BEGIN
        RAISERROR ( 'No se puede eliminar una persona con el nombre Juan',  16, 10)
    END
END
GO