How to create a Rigger that is fired every time you have an insert event in the table?

Asked

Viewed 7,608 times

5

I need to create a Trigger that is triggered every time there is an insertion event in the table, before the insertion is made, it checks a condition, if the condition is met it changes the value of a certain field 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’ve been researching some models like how to build Rigger, but I don’t know how to work on the issue of her manipulating the field being inserted, and changing the value.

sql-server trigger

  • You must use a Rigger INSTEAD OF, and in it you manipulate the data by selecting the data sent to the tables inserted and deleted, and in an update the table inserted will have the new data and table deleted will have the old data records.

  • I put the answer below to your question, working 100% but I would recommend doing this via stored Procedure and run by an external software, because Trigger consumes a lot of resources.

  • Your answer will burst if more than one line is added to the same index, I posted mine.

2 answers

2


@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

  • I’m sorry, but I know very little about Rigger, I created one here that does what I need: http://pastebin.com/ZTJMzYS4 It works exactly the way I need it, but on the question of popping, could explain it better, @Marciano.Andrade? Or how this code adapted to your?

  • 1

    For example, if you use an Insert this way: INSERT INTO table(Col1, Col2) Values ('1','2'), ('2', '3') the inserted table will have two records, because in Insert two records were passed. Another example would be to run an Insert from a Select that returns more than one record. Therefore, the following section of your Rigger will cause an error: SELECT @IDPAI = i.idPai FROM inserted i; because the query in the inserted parent table returns more than one record. I will edit my answer so that it can fit your Rigger.

  • 1

    Continue: Correcting me: The excerpt SELECT @IDPAI = i.idPai FROM inserted i; will not burst, but will catch the last idPai that comes in the inserted, so that some records are not processed by your Rigger. Take a look at this Pastebin: http://pastebin.com/vsU1f89s

  • I understood friend, then, how would be the code adapted to my Trigger?

  • Is there any way to pass me the table structure? ai I will modify correctly...

  • Here is the basic explanation: http://pastebin.com/AmHBFhBY I set up this basic structure.

  • @juniorb2ss I added the modified Trigger, it uses a cursor to interact between each line of the inserted.

  • Thank you, friend, I believe that made Trigger more professional. Apparently it’s working properly, then I’ll switch to the real table, and test it in production. I thank you for the strength, anything I answer again here!!!

  • @juniorb2ss I made one more modification, because I was doing a Join on that update of yours and I was half-hearted about it rsrs. can test again?

Show 4 more comments

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 SQL TRIGGERS are very useful, it is not advisable to use this type of instruction, because it impairs the performance.

  • Thanks for your help, buddy!

Browser other questions tagged sql-server trigger

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