Solved

Creating Trigger working as Event

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

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

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

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.
Active Directory replication delay is the cause to many problems.  Here is a super easy script to force Active Directory replication to all sites with by using an elevated PowerShell command prompt, and a tool to verify your changes.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

792 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