CODEWITHSUNDEEP
sqlsql-server
Redskin
Redskin

Reputation: 37

SQL Inserted table trigger

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!

Upvotes: 0

Views: 132

Answers (2)

Thiyagu
Thiyagu

Reputation: 1330

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

Upvotes: 0

Srinika Pinnaduwage
Srinika Pinnaduwage

Reputation: 1042

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

Upvotes: 0

Related Questions