SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Trigger Help		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Sonu619
Posting Yak Master

195 Posts
Posted - 08/02/2012 :  13:08:53  Show Profile  Reply with Quote


Hi Guys,

I need help. I know i am doing something wrong here. Here is the situation. I created a Trigger on table evertime City field Inserted call this trigger
and insert same city in different table. When i test and insert one by one Trigger works fine and populate table, However if i tried to Insert four rows
trigger run once and populate only one record insted of four. Below is Trigger.


ALTER TRIGGER [dbo].[INSERT_ON_LEFT_TEST]
ON [dbo].[LEFT_TEST]
FOR INSERT
AS
BEGIN
Declare @City varchar(50)
Select @City = city from LEFT_TEST
SET NOCOUNT ON;

SELECT @CITY = CITY FROM inserted
INSERT INTO TRIGGER_TEST (CITY)
SELECT @CITY

END

Please help me out where i am wrong. Thank You.

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 08/02/2012 :  13:14:11  Show Profile  Reply with Quote
The trigger gets called only once per insert, no matter how many rows are inserted. And, a variable can hold only one value - so your @CITY variable is unable to store the data when more than one row is inserted in a single operation. So change your trigger to this: 
ALTER TRIGGER [dbo].[INSERT_ON_LEFT_TEST]
ON [dbo].[LEFT_TEST]
FOR  INSERT
AS
BEGIN
	INSERT INTO TRIGGER_TEST
	( CITY)
	SELECT CITY FROM INSERTED;
END
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47157 Posts
Posted - 08/02/2012 :  13:14:53  Show Profile  Reply with Quote
thats because you've written trigger to only process one row at a time so for batch inserts it wont work. so you need to rewrite it as


ALTER TRIGGER [dbo].[INSERT_ON_LEFT_TEST]
ON [dbo].[LEFT_TEST]
FOR INSERT
AS 
BEGIN

SET NOCOUNT ON;
INSERT INTO TRIGGER_TEST (CITY)
SELECT city
FROM INSERTED
END


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000