trigger - if column updates, insert a record

Looks like you're missing a BEGIN and END statement.

USE [GPSOL]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[UpdatedImeiNumber]
ON [dbo].[Newtbl_DeviceMaster] -- table name
AFTER UPDATE
AS 
declare @imeiNumber as varchar(50) = ''

begin
IF EXISTS (
    SELECT
       I.nIMEINo
    FROM
        INSERTED I
        JOIN
        DELETED D
            -- make sure to compare inserted with (same) deleted IMEI
            ON D.nIMEINo = I.nIMEINo 
            AND D.nIMEINo <> I.nIMEINo -- only IMEI with changed name

    )  
BEGIN

	select @imeiNumber = (SELECT nIMEINo FROM   Inserted)

print 'inserting dummy record'
	insert into tblCommonTrackingData (vSequenceID, vpkDeviceID, dGPSDateTime)
	values('1', @imeiNumber, GETDATE())
END
	end

Select allOpen in new window

You're evaluating an if statement to see if your record exists. That would explain why the record is always inserted, not just when the IMEI number is changed. I suspect the select statement isn't properly executed for the same reason.

You can rewrite your select statement for the variable like this:

Select @imeiNumber = nIMEINo
From Inserted

Select allOpen in new window

Maybe that's why the assignment isn't working as expected.

I can't understand your join criteria. How can a record be equal and different at same time??
ON D.nIMEINo = I.nIMEINo AND D.nIMEINo <> I.nIMEINo

thanks

Vitor... i got the logic from here
http://stackoverflow.com/questions/10550277/how-to-determine-if-anything-changed-in-update-trigger-in-t-sql

I did try having a nested begin end, but the record would not insert
...just tried your code and it didn't insert into the DB

Just a comment..

You've got a major design flaw in your model: An IMEI cannot change.

ste5an, you are correct but you can put a different sim card in a different phone
so you must update the table to say which IMEI the sim card is using

A phone can have more than one IMEI..

and, my first statement was not correct. An IMEI can change. But much worse: 10% of the IMEIs in the 02 (BT Cellenet) were not unique.

So I have to change my my objection: An IMEI is a pretty ugly identifer for direct usage in a relational database.

But another problem: Triggers are executed per statement in SQL Server, thus your trigger will fail at select @imeiNumber = (SELECT nIMEINo FROM   Inserted).

Can you give us more context please?

ste5an, ok pointless discussion since its irrelevant to the question

@webss, if you verify in that example they aren't using the same column names:
ON D.ID = I.ID AND D.NAME <> I.NAME
as you can see they are using ID and Name, so will find records with same ID but different Names.

oh i see
what code would you suggest i use?

Vitor you are a genius!

Please, don't exaggerate :)
Thanks.

As I already wrote, this kind of trigger is erroneous. Triggers are executed per statement.

USE tempdb;
GO

CREATE TABLE dbo.Newtbl_DeviceMaster ( nIMEINo INT );
CREATE TABLE dbo.tblCommonTrackingData
    (
      vSequenceID VARCHAR(255) ,
      vpkDeviceID VARCHAR(255) ,
      dGPSDateTime DATE
    );
GO

CREATE TRIGGER dbo.UpdatedImeiNumber ON dbo.Newtbl_DeviceMaster
    AFTER UPDATE
AS
    BEGIN
        DECLARE @imeiNumber AS VARCHAR(50) = '';

        SELECT  @imeiNumber = ( SELECT  nIMEINo
                                FROM    Inserted
                              );

        -- Check if the IMEI was changed
        IF NOT EXISTS ( SELECT  1
                        FROM    DELETED
                        WHERE   nIMEINo = @imeiNumber )
            INSERT  INTO dbo.tblCommonTrackingData
                    ( vSequenceID ,
                      vpkDeviceID ,
                      dGPSDateTime
                    )
            VALUES  ( '1' ,
                      @imeiNumber ,
                      GETDATE()
                    )
    END;
GO

INSERT  INTO Newtbl_DeviceMaster
        ( nIMEINo )
VALUES  ( 1 ),
        ( 2 ),
        ( 3 );
GO

SELECT  *
FROM    dbo.Newtbl_DeviceMaster;

UPDATE  T
SET     nIMEINo = nIMEINo + 10
FROM    Newtbl_DeviceMaster T
WHERE   T.nIMEINo IN ( 2, 3 );

SELECT  *
FROM    dbo.Newtbl_DeviceMaster;

SELECT  *
FROM    dbo.tblCommonTrackingData TCTD;
GO

DROP TABLE dbo.Newtbl_DeviceMaster;
DROP TABLE dbo.tblCommonTrackingData;
GO

Select allOpen in new window

Thus it will fail with:

(3 row(s) affected)

(3 row(s) affected)
Msg 512, Level 16, State 1, Procedure UpdatedImeiNumber, Line 8
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

Select allOpen in new window

You could also use IF UPDATE(Imei_Number) while updating, to do the update, only when, this field is updated, and not otherwise.

My 2 cents.
HTH.

Sure, the solution I gave only works for one record been updated. But the author didn't say nothing about massive updates. For that the solution should be a little bit different.

correct... this table is rarely updated, and 1 at a time