How to set value in inserted table 1

[sujosh]

I am rewriting triggers in Oracle to SQL Server and I am running into some problems

In Oracle I can change the column SUBS1 new value to a substring of another columns value
:new.SUBS1:= SUBSTRnew.MR_ADAM_KEY,1,9)

In SQL I have tried something like this
Declare @as_temp varchar(20)
select @as_temp = Inserted.MR_ADAM_KEY from Inserted
Set Inserted.SUBS1 = SUBSTR(@as_temp,1,9)

I get an error near the period (.). I think it is due to incorrect syntax can someone point me to a place where I can find out how to do this? I have looked in BOL and I dont see refrences in setting a new value in an inserted table.

Thanks

 

[ESquared]

- Isn't it SUBSTRING not SUBSTR?
- I think you don't need an intermediate variable. In fact, your trigger is assuming only one row is being inserted but there could be many, and using a variable like this would update all inserted rows to the value retreived from the first row.
- Use UPDATE.

UPDATE Inserted SET SUBS1 = SUBSTRING(MR_ADAM_KEY,1,9)

Let us know if that doesn't work!

 

[ESquared]

P.S. you can use the simpler
= LEFT(MR_ADAM_KEY,9)

 

[swampBoogie]

SQL does not support row triggers as Oracle do. SQL server has statement triggers. The inserted/deleted tables are strictly read only. To achieve what you wish you need to update the event table.

update t set subs1 = substring(i.mr_adam_key,1,9)
from t join inserted i on t.pk = i.pk

 

[sujosh]

update t set subs1 = substring(i.mr_adam_key,1,9)
from t join inserted i on t.pk = i.pk"

I tried as you suggested but when an insert occurs it fails saying " Invalid object name t"

Any suggestions?

Thanks

 

[SQLSister]

IS your table named t? If not replace it with your table's actual namce.

 

[ESquared]

Thanks SwampBoogie, I didn't know that, I was just focusing on syntax...

 

[sujosh]

Thanks Swampboogie!!