Solved

trigger - if column updates, insert a record

Posted on 2014-11-17
17
253 Views
Last Modified: 2014-11-18
I am trying to create a trigger which inserts a record if a column name changes

so far i have this code:
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

    )  

	select @imeiNumber = (SELECT nIMEINo FROM   Inserted)

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

Open in new window


However, the IMEINumber is never being assigned from this line
select @imeiNumber = (SELECT nIMEINo FROM   Inserted)

Open in new window


the record is being inserted by the IMEI number is always blank
Also, the record is being inserted if i update other columns and not just IMEI number

what am i doing wrong
0
Comment
Question by:websss
  • 7
  • 5
  • 3
  • +2
17 Comments
 
LVL 12

Expert Comment

by:Koen Van Wielink
ID: 40446965
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

Open 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

Open in new window

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

Expert Comment

by:Vitor Montalvão
ID: 40446993
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
0
 

Author Comment

by:websss
ID: 40447064
0
 

Author Comment

by:websss
ID: 40447067
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
0
 
LVL 32

Expert Comment

by:ste5an
ID: 40447068
Just a comment..

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

Author Comment

by:websss
ID: 40447071
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
0
 
LVL 32

Expert Comment

by:ste5an
ID: 40447074
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?
0
 

Author Comment

by:websss
ID: 40447078
ste5an, ok pointless discussion since its irrelevant to the question
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40447081
@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.
0
 

Author Comment

by:websss
ID: 40449154
oh i see
what code would you suggest i use?
0
 
LVL 45

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 40449199
Let's review your question then.
I am trying to create a trigger which inserts a record if a column name changes
To be more accurate, is not when a column name changes but when occurs a record update.

However, the IMEINumber is never being assigned from this line
(...)
the record is being inserted by the IMEI number is always blank
Yes, because the previous SELECT won't return any records because of the WHERE clause isn't correct.

Also, the record is being inserted if i update other columns and not just IMEI number
Of course. The UPDATE trigger fires when any of the columns is updated and not only a specific one.

Try this code:
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 
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 tblCommonTrackingData (vSequenceID, vpkDeviceID, dGPSDateTime)
           	values('1', @imeiNumber, GETDATE())
end

Open in new window

0
 

Author Closing Comment

by:websss
ID: 40449248
Vitor you are a genius!
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40449255
Please, don't exaggerate :)
Thanks.
0
 
LVL 32

Expert Comment

by:ste5an
ID: 40449264
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

Open 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.

Open in new window

0
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 40449273
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.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40449276
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.
0
 

Author Comment

by:websss
ID: 40449288
correct... this table is rarely updated, and 1 at a time
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now