[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Creating Trigger working as Event

Posted on 2013-11-04
4
Medium Priority
?
303 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 66

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 2000 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 2000 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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
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.
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Suggested Courses

656 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