sql server trigger for insert

Question

I am new to Sql Server but I have always used Oracle a lot. I see that some functionalities are not suppported in Sql Server. Lets say, I want to watch a table for insertion, and what is inserted into that table, I want to copy that same inserted row into another table.

here is my code

Create TRIGGER Post_Trigger ON Posts For Insert
AS
INSERT INTO EmailQueueRaw (UserID,CreatedBy,EmailTypeId,EmailTablePrimaryKey)    VALUES('','Arif','1','1');
GO

In Oracle, I used to use New and Old function which are great. But we dont have it in Sql Server and I am not sure what to do here. Please help me how to copy the same data to another table?

Damien_The_Unbeliever · Accepted Answer · 2013-04-10 08:19:21Z

4

You would use INSERTED (and, if needed DELETED) but you need to be aware that they are pseudo-tables and could contain 0, 1, or multiple rows:

Create TRIGGER Post_Trigger ON Posts For Insert
AS
INSERT INTO EmailQueueRaw (UserID,CreatedBy,EmailTypeId,EmailTablePrimaryKey)
SELECT '',ColumnA,'1',ColumnB FROM inserted;
GO

answered Apr 10, 2013 at 8:19

Damien_The_Unbeliever

241k28 gold badges358 silver badges470 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Arif YILMAZ Over a year ago

how can it have multiple rows? if the case was update or delete statement, there might be multiple rows, but not on insert. because on insert there is only one row inserted at a time, but update and delete might affect multiple rows.

Damien_The_Unbeliever Over a year ago

@Borsel - In general an INSERT can affect multiple rows. SQL Server syntax for all versions allows INSERT ... SELECT (in fact, I've shown one inside the trigger in my answer). ANSI standard syntax allows multiple tuples in the VALUES clause, e.g. INSERT INTO Table(A,B) VALUES (1,2),(3,4) and is supported since 2008 version (IIRC). Oracle has long supported the ANSI standard syntax, I believe.

Damien_The_Unbeliever Over a year ago

@Borsel (Actually, on further reflection, I believe INSERT ... SELECT is also allowed by the standard, it's not an SQL Server only construct)

Collectives™ on Stack Overflow

sql server trigger for insert

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related