CODEWITHSUNDEEP
sql-serversql-server-2008
Muhammad Faisal Ameer
Muhammad Faisal Ameer

Reputation: 1

Sql Server 2008 Triggers

Table Message already has a trigger. Running successfully inside that trigger I have an insert statement:

Insert into tblSettlement (MessageID,Lat,Long) Select Messageid,y,x from inserted

On the table tblSettlement I have placed that trigger:

 ALTER TRIGGER [dbo].[AddSettlementOnINSERT]
 ON [dbo].[TblSettlement]
 After INSERT
  AS
 DECLARE @id Bigint, @Lat Float,@Long Float, @LocName Varchar(200), @Dist float,@upd    bit 
 Set @upd = (SELECT updated FROM inserted)
 SET @id = (SELECT MessageID FROM inserted)
SET @lat = (SELECT [Lat] FROM inserted)
SET @Long = (SELECT [Long] FROM inserted)
if (@upd = 0) 
begin
declare @table table 
(Location Varchar(200),Distance float)
insert into @table 
SELECT  top 1 Full_Name_nd, SQRT(
  POWer(69.1 * (lat - @lat), 2) +
   POWer(69.1 * (@long - long) * COS(lat / 57.3), 2))As distance
 FROM geodb.dbo.geonames where SQRT(
  POWer(69.1 * (lat - @Lat), 2) +
  POWer(69.1 * (@Long - long) * COS(lat / 57.3), 2)) < 1 
 set @LocName = (select location from @table)
 set @Dist = (select distance from @table)
 Insert into dbo.tblset2

 (Messageid,lat,long,settlement,distance)values(@id,@lat,@long,@locName,@Dist)
  end

However the problem is that whenever we enable above trigger, insertion stops working on Message table.

Meaning: we want to insert, after insertion taking few column values and try to update another table with in the same Table's Trigger. Which actually does not allow us to insert.

Upvotes: 0

Views: 591

Answers (1)

marc_s
marc_s

Reputation: 754928

Your basic assumption about SQL Server triggers is wrong - you seem to be assuming (as many people do) that the trigger will be called once per row inserted - that is NOT the case!

The trigger will be called once per statement - that is, if your statement insert 20 rows at once, your trigger will be called once, and the pseudo table Inserted will contain 20 rows in it.

As such, if Inserted contains multiple rows, your statement here will either fail miserably, or select an arbitrary, random entry:

DECLARE @id Bigint, @Lat Float,@Long Float, @LocName Varchar(200), @Dist float,@upd    bit 
Set @upd = (SELECT updated FROM inserted)
SET @id = (SELECT MessageID FROM inserted)
SET @lat = (SELECT [Lat] FROM inserted)
SET @Long = (SELECT [Long] FROM inserted)

You have to rewrite your trigger entirely, taking into account that Inserted can and will contain multiple rows.

Upvotes: 5

Related Questions