Solved

Creating Trigger working as Event

Posted on 2013-11-04
4
291 Views
Last Modified: 2013-11-06
Hi, Experts

I need to create trigger On engine on / off event but it create more rows i just want to insert when engine on / off .
I am sending my Code Please check and correct my code.

USE [gpssystem]
GO
/****** Object:  Trigger [dbo].[InsertOnOffEvent]    Script Date: 11/04/2013 13:59:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



ALTER TRIGGER [dbo].[InsertOnOffEvent] ON [dbo].[gpsobject] 
FOR UPDATE
AS

If UPDATE (engine) 
declare  @devicenum varchar(20),@latitude decimal(18, 4),@longitude decimal(18, 4)
declare @systime datetime,@speed int ,@miles decimal(18,2),@engine int ,@eventcode varchar(50)

-------------------------------------------------------------
select @devicenum=devicenum,
@latitude=latitude,
@longitude=longitude,
@systime=systime,
@speed=speed,
@eventcode=eventcode,
@miles=miles,
@engine=engine
from inserted
-----------------------------------------------------------


IF @engine='1'

insert into gpshistory (devicenum,latitude,longitude,systime,gpsdevicetime,speed,eventcode,miles,engine)
values(@devicenum,@latitude,@longitude,@systime,@systime,@speed,'4',@miles,@engine)
------------------------------------------------------------

IF @engine='0'

insert into gpshistory (devicenum,latitude,longitude,systime,gpsdevicetime,speed,eventcode,miles,engine)
values(@devicenum,@latitude,@longitude,@systime,@systime,@speed,'12',@miles,@engine)

	PRINT 'AFTER UPDATE Trigger fired.'

Open in new window

0
Comment
Question by:Asrar Azmi
  • 2
4 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39621338
>trigger On engine on / off event
Define 'on / off event'.  After re-reading this a couple of times, no idea what you are asking.
0
 
LVL 9

Accepted Solution

by:
auke_t earned 500 total points
ID: 39621394
ALTER TRIGGER [dbo].[InsertOnOffEvent] ON [dbo].[gpsobject] 
FOR UPDATE
AS


IF ((SELECT engine FROM DELETED) = 0 AND (SELECT engine FROM INSERTED) = 1) OR
 ((SELECT engine FROM DELETED) = 1 AND (SELECT engine FROM INSERTED) = 0)
BEGIN

declare  @devicenum varchar(20),@latitude decimal(18, 4),@longitude decimal(18, 4)
declare @systime datetime,@speed int ,@miles decimal(18,2),@engine int ,@eventcode varchar(50)

-------------------------------------------------------------
select @devicenum=devicenum,
@latitude=latitude,
@longitude=longitude,
@systime=systime,
@speed=speed,
@eventcode=eventcode,
@miles=miles,
@engine=engine
from inserted
-----------------------------------------------------------


IF @engine='1'

insert into gpshistory (devicenum,latitude,longitude,systime,gpsdevicetime,speed,eventcode,miles,engine)
values(@devicenum,@latitude,@longitude,@systime,@systime,@speed,'4',@miles,@engine)
------------------------------------------------------------

IF @engine='0'

insert into gpshistory (devicenum,latitude,longitude,systime,gpsdevicetime,speed,eventcode,miles,engine)
values(@devicenum,@latitude,@longitude,@systime,@systime,@speed,'12',@miles,@engine)

	PRINT 'AFTER UPDATE Trigger fired.'
END

Open in new window

0
 
LVL 9

Assisted Solution

by:auke_t
auke_t earned 500 total points
ID: 39621399
Ok, so far as I understand you update only a single record at a time?
And you only want the trigger to activate when the engine actually changes value, either from 0 to 1 or from 1 to 0?
0
 

Author Comment

by:Asrar Azmi
ID: 39626750
Thanks alot its working good.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now