INSER lock trigger with condition and error message

Asked

Viewed 335 times

0

I would like to create a Trigger on a condition that only does not generate payment launches for a system-specific financial responsibility code and that the others let it pass and when it did not generate for the code an error message appears on the user.

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

1 answer

0


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

When building trigger procedures it is necessary to be aware that they are triggered to handle one or more lines, or even none. At first I recommend reading the series of articles "Traps in trigger procedure programming”.

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

Here is code with 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; it may contain error(s).

Browser other questions tagged sql-server

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