Trigger with null value

Asked

Viewed 453 times

0

Good evening guys, I need you to check if the address and phone columns are null, if you are returning an error message, otherwise effective operation. my code is like this:

CREATE TRIGGER VERIFICA
ON cliente
INSTEAD OF INSERT AS
BEGIN
DECLARE
@NOME VARCHAR(50),
@TELEFONE VARCHAR(12),
@ENDERECO VARCHAR (100)

SELECT @NOME = NOME FROM inserted
SELECT @TELEFONE = TELEFONE FROM inserted
SELECT @ENDERECO = ENDERECO FROM inserted

IF @telefone is not null and @endereco is not null
begin
    RAISERROR ('Campos nulos', 11,1);
end
ELSE
    INSERT INTO cliente (nome, telefone, endereco) VALUES (@NOME, @TELEFONE, @ENDERECO);
END
sql sql-server

  • This could not be done directly in the application, thus avoiding using trigger procedure (Trigger) for something so simple?

  • It is necessary to be aware that a trigger procedure (Trigger) SQL Server has to be built to handle multiple lines at the same time. The above code considers that there will be a single line, which can lead to an execution error. Suggested reading: https://portosql.wordpress.com/2018/08/18/armadilhas-na-programacao-de-trigger/

  • It is actually easier to treat nulls in the application itself than in SQL, unless you have a very strong reason to do so!

  • Yes, it would be much more interesting to do this in the client service but it’s just a question of university.

2 answers

0


Has a small correction in logic and syntax according to MSDN.

CREATE TRIGGER VERIFICA
ON CLIENTE
FOR INSERT
AS

DECLARE
   @NOME VARCHAR(50),
   @TELEFONE VARCHAR(12),
   @ENDERECO VARCHAR (100)

SELECT @NOME = (Select Nome from inserted)
SELECT @TELEFONE = (Select Telefone from inserted)
SELECT @ENDERECO = (select Endereco FROM inserted)

IF (@nome is null or @telefone is null or @endereco is null)
begin
    RAISERROR ('Campos nulos', 11,1);
end
ELSE
begin
    INSERT INTO cliente (nome, telefone, endereco) VALUES (@NOME, @TELEFONE, @ENDERECO);
END

To generate my scenario created I illustrated with the client table:

create table cliente 
(  
   nome varchar, 
   telefone varchar, 
   endereco varchar, 
   id int identity(1,1) primary key
)

And the test cases:

INSERT INTO cliente (nome, telefone, endereco) VALUES (null,null,null);
INSERT INTO cliente (nome, telefone, endereco) VALUES (null,'b','c');
INSERT INTO cliente (nome, telefone, endereco) VALUES ('a','b',null);
INSERT INTO cliente (nome, telefone, endereco) VALUES ('a',null,'c');

Outgoing:

Msg 50000, Level 11, State 1, Procedure VERIFICA, Line 17 
[Batch Start Line 28]

Campos nulos

0

CREATE TRIGGER VERIFICA
ON cliente
AFTER INSERT
AS
BEGIN
    IF Exists(
        Select inserted.campoSChave
        From inserted
        Where telefone is null Or endereco is null
    )
    Begin
        RAISERROR ('Campos nulos', 11,1);
        Rollback;
    End
END

Note that I changed the type of Trigger Instead Of for AFTER.

In the inserted.field, can be any field of the source table.

Note that as an alternative you can create a Contrain in the table design where you can put the following syntax:

Not (Telefone Is Null Or Endereco Is Null)

Browser other questions tagged sql sql-server

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