?
Solved

Creating Trigger working as Event

Posted on 2013-11-04
4
Medium Priority
?
302 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
A quick Powershell script I wrote to find old program installations and check versions of a specific file across the network.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

777 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