URGENT!! PLEASE HELP WITH TRIGGER

[MickeD]

Hi,

I need to create insert/update trigger that checks some value in another table and put the result in one of the fields in the updated table (after insert/update process)
Here is a code i try:

CREATE TRIGGER TR_Check_IF_Arrived ON tbl_Test
FOR INSERT, UPDATE
AS

declare @stat char(2)

select @stat=m.status_cd
from tbl_Main m, inserted i
where s.rec_id=i.ship_id

if @stat in ('02','03','04','05')
update inserted set IsArrived=1

GO


the error i get says:
"The logical tables INSERTED and DELETED cannot be updated."

I can update the table (tbl_test) directly, i need overwrite the value in the field IsArrived in inserted

So how can i do it?

 

[vishnuprasad]

U cant make updates into the inserted table.I can just give a idea of creating a unique column in the exiting table or create a temp tab with unique val and go with the updates with the table directly.I have'nt checked the following modified query, but just work on it.

CREATE TRIGGER TR_Check_IF_Arrived ON tbl_Test
FOR INSERT, UPDATE
AS

declare @stat char(2)
declare @val int

select @stat=m.status_cd,@int = 'some unique column'
from tbl_Main m, inserted i
where s.rec_id=i.ship_id



if @stat in ('02','03','04','05')
--update inserted set IsArrived=1
update tbl_insert set Isarrived = 1
where unique Column = @int

GO