SQL Inserted table trigger

0
votes

If I run the following select statement inside an insert trigger, is it possible that it will return more than one result?: DECLARE @variable char(1) = (SELECT ID FROM inserted) If so, then what's the best to handle it?

Here is the problem that I am trying to solve: Every time when the new record is inserted into a table, I want to take the newly inserted ID and insert it into another table(if it doesn't exists).

Thank you!

2
sqlsql-server
It is not only possible but highly likely and such code is simply wrong (because there is a bug waiting to happen). The correct way to handle it is not to assign values from inserted into a variable. You need more information in your question for a better answer. - Gordon Linoff
you can use SCOPE_IDENTITY() - user3510665
Apart from that SCOPE_IDENTITY() returns a scalar value, @user3510665 , and a INSERT can affect between 1 and ∞ rows. If 2 are more are inserted (which they can and will be) the same issue as assigning values to variables will occur. inserted contains a data set, and should be treated as a data set. - Larnu
Use INSERT...SELECT...FROM inserted WHERE NOT EXISTS or a similar MERGE statement to conditionally insert potentially multiple rows from inserted. - Dan Guzman

2 Answers

0
votes

Instead of 

DECLARE @variable char(1) = (SELECT ID FROM inserted)

You can do something like following:

Declare @VarTempTbl as table (id int)

Insert into @VarTempTbl (id)
Select id from inserted

So that you can get those values for further processing

0
votes

Now, I had created Two tables One for Master table and another for When any Insertion happens in that Master table, that entry has to inserted into the another table.

CREATE TABLE tblEmployee
(
Id int Primary Key,
Name nvarchar(30),
Gender nvarchar(10),
DepartmentId int
)

CREATE TABLE tblEmployee_New
(
Id int Primary Key,
Name nvarchar(30),
Gender nvarchar(10),
DepartmentId int
)

Trigger:

CREATE TRIGGER TR_EMPLOYEEDETAILS_AFTEROFINSERT
ON TBLEMPLOYEE
AFTER INSERT
AS
BEGIN

TRUNCATE TABLE tblEmployee_New

INSERT INTO TBLEMPLOYEE_NEW(ID, NAME, GENDER, DEPARTMENTID)
SELECT ID, NAME, GENDER, DEPARTMENTID
FROM INSERTED 

END

Now Lets try to insert into record into a master table

Insert into tblEmployee values (1,'John', 'Male', 3)
Insert into tblEmployee values (2,'Mike', 'Male', 2)

It has automatically insert the newly inserted records into the another table. If your want to remove the Previous records then add a drop Statement in that above Trigger.

Note: You can also use #Temp Table instead of creating a another table('tblEmployee_New')

Kinldy Share your comments