Trigger Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Trigger Help

Author

Topic

Sonu619
Posting Yak Master

202 Posts

Posted - 2012-08-02 : 13:08:53

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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-02 : 13:14:11

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-02 : 13:14:53

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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources