How do I create a trigger that fires whenever I have an insert event in the table?

Question

How do I create a trigger that fires whenever I have an insert event in the table?

Navigation

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

5

I need to create a trigger that fires whenever I have an insert event in the table, before it is inserted, it checks a condition, if the condition is respected it changes the value of a certain field that is being inserted.

Example:

-- id do registro atual que esta sendo inserido 
if id_pai = 0 or null
 -- ela pega este ID, faz um select em outra tabela ( select id from tabela2 where id_nota = id_nota.
 -- pega o resultado que seria o ID do registro da tabela2 e insere no campo 
 -- id_pai do registro que esta sendo inserido.
end;

I have been researching some models like build the trigger, however, I do not know how to work on the issue of manipulating the field being inserted, and change the value.

sql-server trigger

asked by anonymous 04.04.2014 / 15:53

2 answers

1

CREATE TRIGGER
    tg_NomeDaTriger
ON
NomedaTabela
AFTER INSERT

AS
DECLARE @ID_PAI int
SELECT top 1 @ID=ID_PAI From NomedaTabela order by CAmpoID desc
if(@ID_PAI=0)
BEGIN
  --sua logica aqui para ser executa
END

Example working perfectly with the NortWind database. If you insert in the Categories table in the CategoryName field the text 'test' will also be inserted 'Leonardo':

CREATE TRIGGER
tg_NomeDaTriger
ON
dbo.Categories
AFTER INSERT

AS
DECLARE @ID_PAI varchar(200)
SELECT top 1 @ID_PAI=CategoryName from dbo.Categories order by CategoryID desc
if(@ID_PAI='teste')
BEGIN
INSERT dbo.Categories (CategoryName,Description) values ('Leonardp','')
END

Remembering that although TRIGGERS SQL is very useful, it is not advisable to use this type of instruction, because it damages performance.

04.04.2014 / 16:49

Transition effect in jQuery Picking characters uniquely in a multiple string

score 2 · Accepted Answer

@edit

CREATE TRIGGER Table_A_Update ON itensNota FOR INSERT
AS
DECLARE @IDPAI VARCHAR(200), @IDNOTA VARCHAR(200), @ID INT
BEGIN
DECLARE cur CURSOR FOR
  SELECT idPai, idNota, ID
    FROM inserted
OPEN cur
FETCH NEXT FROM cur INTO @IDPAI, @IDNOTA, @ID
WHILE @@FETCH_STATUS = 0
BEGIN
        IF ( @idPai = 0 )
                        UPDATE itensNota
                        SET idPai = ( SELECT id FROM nota WHERE idNota = @IDNOTA )
                        FROM itensNota a
                        where a.id = @ID and a.idNota = @IDNOTA
        FETCH NEXT FROM cur INTO @IDPAI, @IDNOTA, @ID
END
CLOSE cur
DEALLOCATE cur
END