SQL Server - Trigger inserted change to updated?

Hi All,


I need to ask i have on trigger on Inserted for example

select @rate = i.VAL21 from inserted;


How can i change it to updated ? i dont find updated ?

Thanks and Regards,
Farrukh Hameed 

Whenever you create trigger on tables, the sql server automatically creates two magic tables named as Inserted and Deleted.

As the name suggestes, inserted keeps value which have been inserted in table or updated in table. Deleted keeps information of rows which have been deleted or have been modified during update command.

There is no updated table. Let us know what exactly are you trying to achieve?

However if you have updated some table and want to retrieve the old table values to log  or to show user about it. In that case you can use the Deleted table. As I have shared already, that it maintains the modified old values too.

Hope it helps.

Vikram Singh Saini ,

Thanks for reply what iam trying to do i have a paramter i.VAL33  i need when ever this column updated then the trigger runs and perform the below calculation it works on insert but not on update?

ALTER TRIGGER [dbo].[TRIGGER] ON [TEST].[dbo].[ITEM_EVENT]
AFTER INSERT,UPDATE

AS
 declare @event_type1 varchar(100);
 declare @item_type varchar(100);
 declare @start_date date;
 declare @item_id varchar(100);
 declare @Parameter decimal(20,10);

 select @event_type1 = i.event_type1 from inserted i;
 select @item_type = it.ITEM_TYPE from inserted i, ITEM it where it.ITEM_ID = i.ITEM_ID;
 select @start_date = i.START_DATETIME from inserted i;
 select @item_id = i.ITEM_ID from inserted i;
 select @Parameter = i.VAL33 from inserted i;

 
 if (@event_type1 = 'VOL' and @item_type = 'SAM') 
 begin
 
 update [TEST].[dbo].ITEM_EVENT
    set VAL3 = (
   SELECT CASE (EVTR.VAL38) WHEN 0 THEN (NULL)ELSE
  CASE (EVTR.VAL38) WHEN NULL then (NULL ) ELSE
  CASE (EVTR.VAL11) WHEN 0 THEN(NULL )ELSE
  CASE (EVTR.VAL11) WHEN NULL THEN (NULL )ELSE
  (EVTR.VAL38)/(EVTR.VAL11 )END END END END
    FROM
   [TEST].[dbo].TABLE_REF EVTR,[TEST].[dbo].ITEM ID
   WHERE EVTR.event_type1 ='WEL'
   AND ID.ITEM_TYPE='COMP'
   AND ID.ITEM_ID=EVTR.ITEM_ID
   AND EVTR.ITEM_ID =ITEM_EVENT.ITEM_ID
   AND EVTR.START_DATETIME=(SELECT MAX(START_DATETIME)
          FROM [TEST].[dbo].TABLE_REF EVTR2
         WHERE START_DATETIME <=ITEM_EVENT.START_DATETIME
           AND EVTR2.ITEM_ID = EVTR.ITEM_ID)
   )* @Parameter
  where [TEST].[dbo].ITEM_EVENT.ITEM_ID = @item_id
    and [TEST].[dbo].ITEM_EVENT.START_DATETIME = @start_date
    and [TEST].[dbo].ITEM_EVENT.event_type1 = @event_type1; 

My Suggestion (give it try...)

(1) Create Update trigger separately on the same table. Don't mix it with After Insert.

(2) Then modify following lines inserted keyword with deleted one as:

select @event_type1 = i.event_type1 from deleted i;
 select @item_type = it.ITEM_TYPE from deleted i, ITEM it where it.ITEM_ID = i.ITEM_ID;
 select @start_date = i.START_DATETIME from deleted i;
 select @item_id = i.ITEM_ID from deleted i;
 select @Parameter = i.VAL33 from deleted i;

(3) Finally test it and let us know.

Hi Vikaram,

Thank you very much now the trigger works for update too?


Thank you
hameed

Glad to know that. :)