Trigger with null value

Question

Trigger with null value

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

Good evening guys, I need to make sure that the address and phone columns are null, in case an error message is returned, otherwise the operation will take effect. my code looks 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

asked by anonymous 06.09.2018 / 00:54

2 answers

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 Trigger type from Instead Of to AFTER .

In inserted.campoSChave , this can be any field in the source table.

Note that you can alternatively create a Contrain in the table drawing where you can put the following syntax:

Not (Telefone Is Null Or Endereco Is Null)

06.09.2018 / 12:13

Problems compiling for static in GO with CGO_ENABLED = 1 Slick with FlexBox CSS3 not working

score 0 · Accepted Answer

It 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, I created it 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');

With output:

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

Campos nulos