broken trigger - 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)

broken trigger

Author

Topic

sydoggkdy
Starting Member

1 Post

Posted - 2010-03-19 : 16:20:26

Hello first time poster so don't shout if I say something wrong lol

oke, I had a trigger that worked fine in MS SQL server but I now have to store my database on PHPMYADMIN and now the trigger won't work, and for the life of me I can't figure it out.

can any one help?

the trigger is

CREATE TRIGGER Store_location
ON LOCATION AFTER UPDATE
AS
BEGIN
DECLARE @IMEI varchar(20), @old_lat float, @old_lon float,

select @old_lat=(select latitude from Deleted)
select @old_lon=(select longitude from Deleted)
select @IMEI=(select IMEI from Deleted)
insert into PATH Values (getdate(), @imei, @old_lat, @old_lon)
END

Kristen
Test

22859 Posts

Posted - 2010-03-19 : 16:27:34

Assuming we are talking about Microsoft SQL Server?

it looks OK - although your INSERT statement is reliant on the PATH table having only 5 columns, in the specific order of your VALUES clause AND no one adding any new columns in the future.

The trigger is also reliant on only one record being updated at a time - so if someone did an UPDATE that matched multiple rows this trigger will INSERT into the PATH table at random

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-20 : 01:45:22

to make it handle batch updates you need


CREATE TRIGGER Store_location 
ON LOCATION 
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON
insert into PATH 
select getdate(),IMEI,latitude,longitude from Deleted
END

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2010-03-20 : 06:43:43

Much better, pointless getting values from DELETED into variables with separate select statements - missed that, must be getting too old!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources