Solved

Creating Trigger working as Event

Posted on 2013-11-04
4
300 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 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

733 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