sql server - Trigger on insert

Question

Hey

never really done a trigger before, looking for a bit of help.

I need to enter a new record into a table using the scope identity when a record is entered into another table.

so
INSERT INTO MyTable (Field1, Field2, field3, Field4)
VALUES ('Val1', 'Val2', 'Val3', 'Val4')

then in the trigger, add a new record to a revision table using the ScopeIdentity() of the record just added into MyTable

can t get my head around the CREATE TRIGGER text...

Thanks

Nacho

Answer 1

Hi,

CREATE TRIGGER MyTrig ON dbo.MyTable AFTER INSERT AS 
    -- Here you can add the queries which you want to execute each time INSERT on table MyTable was executed
    -- Note! Triggers are NOT something you can count on! For example bulk Insert does not fire triggers by default, which mean that someone can INSERT rows and the trigger will not be executed!

    -- DML triggers use the **deleted** and **inserted** logical tables.
    -- The inserted table includes the data of the rows which you INSERT to the table
    -- Therefore, you can simple use INSERT to the revision table from the inserted table

    INSET revision (<column list>) SELECT <Column list> from inserted

GO  

Answer 2

To expand on what Ronen says, here is a very simple example to illustrate some key points:

CREATE TABLE MyTable (id   int NOT NULL IDENTITY,
                      data varchar(23) NOT NULL)
go
CREATE TRIGGER MyTable_tri ON MyTable AFTER INSERT AS
   SELECT * FROM inserted
go
INSERT MyTable(data) 
   VALUES('Primo'),
         ('Segundo'),
         ('Tercero')
go
DROP TABLE MyTable

• The trigger fires once per statement, so you need to account for that there may be multiple rows in the inserted virtual table.
• You don't need scope_identity(), instead you find the ids in inserted.
• The table inserted is only visible in the trigger itself, not in any stored procedures it calls.
• Normally, you don't have a SELECT in a trigger like I have that above. It is something you may temporarily may add for debugging purposes, but it is nothing you would have in production code.

Answer 3

Hi @NachitoMax
The key point of this issue is how you can get the inserted values and use them later.
Therefore, we need to introduce two special tables: inserted & deleted.
Naturally, inserted will contain rows only for INSERT and UPDATE triggers, and it will be empty for DELETE triggers. Similarly, deleted will contain rows only for DELETE and UPDATE triggers, and it will be empty for INSERT triggers.
So the code maybe like:

CREATE TRIGGER Trigger_Test ON MyTable   
FOR /*You can use either the keyword FOR or the keyword AFTER to define an AFTER trigger*/  
INSERT AS   
INSERT INTO AnotherTable (column1,column2,...) SELECT column1,column2,... FROM inserted      

For more details about CREATE TRIGGER, please refer to this document: CREATE TRIGGER (Transact-SQL)
For some examples about TRIGGER AFTER INSERT, please refer to this article: SQL Server trigger after insert with examples

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.