Trigger help - getting a column value of the just updated record

Can anyone help with this trigger

does the code look ok? and can you help with the commented out lines with proper t-sql

This is what is supposed to happen
if IMEI column updated
search another table for any records from this IMEI number
If none found, then insert some dummy data in this table


THis is what i have so far:
CREATE TRIGGER trig_InsertDummyData
ON Newtbl_DeviceMaster
FOR UPDATE
AS BEGIN
   if update(nIMEINo) begin
   
	  --if exists (select top(1) from dataTable where Imei = @thatImeiNumberThatWasJustUpdated)
         -- Begin
	  -- insert into dataTable(imei) Values(@thatImeiNumberThatWasJustUpdated)
         --end
   end
END

Open in new window


How do i get @thatImeiNumberThatWasJustUpdated value?
websssAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
Insert into ...
Select  ...
From INSERTED I
where Not exists ( select null from othertable o where o.imei = I.imei )

Above is the basic syntax
0
websssAuthor Commented:
So: "INSERTED" is the row that was just updated?

Its not on insert record, its on update record
0
ste5anSenior DeveloperCommented:
No, there are two direct problems:

1. UPDATE(columnName) is true when the column was part of an INSERT or UPDATE statement. In the INSERT case it is correct. But in the UPDATE case part means its true for this statement:

UPDATE tableName
SET columnName = columnName;

Open in new window


It will update every row and set it to the previous value. Infact it does not change (or update) in reality anything. But UPDATE(columnName) is true. In all normal circumstance we only want to execute triggers on real changes. Thus the condition must be changed or the test must be pushed downwards. We see an example later.

2. Triggers are once executed per statement. Thus more then one row may be affected. E.g. in the sample above all rows are changed. Thus we must handle this situation be using the virtual tables DELETED and INSERTED.

An example:

USE tempdb;
GO

CREATE TABLE UpdateTest
    (
      ID INT ,
      Payload NVARCHAR(8) ,
      UpdateDetectedByColumnsUpdated BIT ,
      UpdateDetectedByUpdate BIT ,
      UpdateDetectedByComparision BIT,

    );
GO

CREATE TRIGGER tr_UpdateTest ON UpdateTest
    AFTER UPDATE
AS
    SET NOCOUNT ON;               

    IF ( COLUMNS_UPDATED() & 2 ) = 2
        UPDATE  T
        SET     UpdateDetectedByColumnsUpdated = 1
        FROM    UpdateTest T
                INNER JOIN Inserted I ON I.ID = T.ID;               

    IF UPDATE(Payload)
        UPDATE  T
        SET     UpdateDetectedByUpdate = 1
        FROM    UpdateTest T
                INNER JOIN Inserted I ON I.ID = T.ID;               

    UPDATE  T
    SET     UpdateDetectedByComparision = 1
    FROM    UpdateTest T
            INNER JOIN Inserted I ON I.ID = T.ID
            INNER JOIN Deleted D ON I.ID = D.ID
                                    AND I.Payload <> D.Payload;
GO

INSERT  INTO UpdateTest
VALUES  ( 1, '1', 0, 0, 0 ),
        ( 2, '2', 0, 0, 0 ),
        ( 3, '3', 0, 0, 0 ),
        ( 4, '4', 0, 0, 0 );

-- Single row update, change of the value.
UPDATE  UpdateTest
SET     Payload = '11'
WHERE   ID = 1;

-- Single row update, no change of the value.
UPDATE  UpdateTest
SET     Payload = '2'
WHERE   ID = 2;

-- Display the results.
SELECT  *
FROM    UpdateTest;

-- Reset the indicators.
UPDATE  UpdateTest
SET     UpdateDetectedByColumnsUpdated = 0 ,
        UpdateDetectedByUpdate = 0 ,
        UpdateDetectedByComparision = 0;

-- Multi-row update, changing only row 1 and 3.
UPDATE  UpdateTest
SET     Payload = '2'
WHERE   ID <= 3;

-- Display the results.
SELECT  *
FROM    UpdateTest;

DROP TABLE UpdateTest;
GO

Open in new window


So your trigger could look like

CREATE TRIGGER trig_InsertDummyData ON Newtbl_DeviceMaster
    FOR UPDATE
AS
    SET NOCOUNT ON;

    IF UPDATE(nIMEINo)
        BEGIN
            UPDATE  DT
            SET     imei = I.nIMEINo
            FROM    DELETED D
                    INNER JOIN INSERTED I ON D.primaryKeyColumns = I.primaryKeyColumns
                    INNER JOIN dataTable DT ON DT.primaryKeyColumns = I.primaryKeyColumns
            WHERE   D.nIMEINo != I.nIMEINo;
        END;

Open in new window


But the problem is: I'm not sure what your logic should be..
0
Scott PletcherSenior DBACommented:
CREATE TRIGGER trig_InsertDummyData
ON dbo.Newtbl_DeviceMaster
AFTER UPDATE
AS
----------------------------------------------------------------------------------------------------
SET NOCOUNT ON;
IF UPDATE(nIMEINo)
BEGIN
    INSERT INTO dbo.dataTable ( imei )
    SELECT d.nIMEINo
    FROM deleted d
    INNER JOIN inserted i ON
        i.key_col = d.key_col
    WHERE
        ISNULL(d.nIMEINo, '-1') <> ISNULL(i.nIMEINo, '-1')
END --IF
----------------------------------------------------------------------------------------------------
GO
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.