INSER lock trigger with condition and error message

Question

INSER lock trigger with condition and error message

Navigation

#1 by (0 votes)

0

I would like to create a Trigger with a condition that just did not generate payment postings for an expecifico financial controller code in the system and that the others let pass and when it did not generate for the expecifico code an error message appeared on the user's screen .

CREATE TRIGGER noInsertTeste

ON FLAN 


INSTEAD OF INSERT 
AS
BEGIN
    SELECT * FROM FLAN WHERE CODTDO = '00017431'

    DECLARE 

    printl ("Lançamentos não gerados pois o Responsavel TESTE não pode ser gerado lançamento.")

END

sql-server

asked by anonymous 01.02.2018 / 14:18

1 answer

How to mount a dynamic combo with angle 5? Search View within a Fragment

score 0 · Answer 1

Charles, so I understand is not to allow inclusion of rows in the FLAN table when the value of the CODEDOS column is "00017431".

When building trigger procedures you need to be aware that they are triggered to treat one or more lines, or none at all. At first I recommend reading the series of articles " Pitfalls in the programming of procedures trigger .

Because the trigger procedure can receive more than one row in the same event, if it is to reject one or more rows, then the whole block is rejected.

Here is a code with an initial suggestion, which can be improved.

-- código #1
CREATE TRIGGER dbo.noInsertTeste
     on dbo.FLAN
     instead of INSERT as
begin

-- verifica número de linhas a tratar
declare @NL int;
set @NL= (SELECT count(*) from (SELECT top (2) * from INSERTED) as I);

-- encerra o processamento se não há linha para tratar
IF @NL = 0 return;

-- rejeita bloco se houver alguma linha com o código específico
IF exists (SELECT * from INSERTED where CODTDO = '00017431')
  begin
  PRINT 'Lançamentos não gerados pois o Responsavel TESTE não pode ser gerado lançamento.';  -- raiserror?
  ROLLBACK TRANSACTION;
  return;
  end;

-- inclui as linhas na tabela FLAN
INSERT on dbo.FLAN 
  SELECT * from INSERTED;

end;
go

I did not have the opportunity to test the code; may contain error (s).