Trigger with IF cycle - 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 2005 Forums

Transact-SQL (2005)

Trigger with IF cycle

Author

Topic

dariopalermo
Starting Member

16 Posts

Posted - 2009-09-10 : 09:18:18

First table: name, lon, lat, date, time
(it holds historic info on vehicles position)
new records are added and the db grows from time to time.

Second table: name, lon, lat, date, time

the trigger now is only capable to, AFTER INSERT in the first table, update the record in the second table where FirstTable.name=SecondTable.name

I want also new vehicles to be added to the second table automatically... is it possible?

I'm modifying the trigger with an IF EXIST... ELSE cycle but I'm not sure if the check is made per record or 1 time on the entire inserted virtual table...

Anyone can help?

The actual trigger code:

AFTER INSERT
AS

UPDATE digigroup.dbo.Pos
Set digigroup.dbo.Pos.Data=sto_data,
digigroup.dbo.Pos.Ora=sto_ora,
digigroup.dbo.Pos.Lat=CAST(sto_latOrig AS float) / 60000 ,
digigroup.dbo.Pos.Lon=CAST(sto_lonOrig AS float) / 60000

FROM inserted
WHERE digigroup.dbo.Pos.dgutenza = sto_utenza

asgast
Posting Yak Master

149 Posts

Posted - 2009-09-10 : 09:30:34

IF EXISTS check is made once on the entire table

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-10 : 09:34:35

[code]
INSERT INTO dbo.Pos (name, lon, lat, date, time)
SELECT i.name, i.lon, i.lat, i.date, i.time
FROM inserted i
WHERE not exists
(
SELECT *
FROM dbo.Pos x
WHERE x.name = i.name
)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

dariopalermo
Starting Member

16 Posts

Posted - 2009-09-10 : 12:10:28

Thanks Kthan... your reply was my salvation ;)

Now I have the insert part righ after the update part. The trigger will update the existing record and then insert the non existing record.

Again, thanks!

Dario Palermo

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources