[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 361
  • Last Modified:

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?
0
websss
Asked:
websss
1 Solution
 
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now