Can insert trigger update record being inserted

[tc]

I think a trigger is the way to go on this but let me try to explain the confusion. I have a table with an id field. Based upon this ID field I need to have a character stripped out of the ID and placed in another column on the same table/same record(Sorry, because of the development env I can't use a computer column). The question is how to do this with an insert trigger. My understanding is the trigger will fire on the insert, but the record isn't there yet. Is there a way to handle this? My code below does not error but also does not update?

create TRIGGER test_Ins_tr
ON table1
FOR update
AS
DECLARE @oid char(1)
declare @actid char(10)
SELECT @oid = SUBSTRING(right(col1,2),1,1), @actid = col1
FROM inserted
update table1
set col2 = @oid where table1.col1 = @actid

[Kurt]

The data you are trying to insert is stored in a psuedo-table called Inserted.
I don't know if you know that already, but just in case. It would be easy if you could just update the Inserted table with the value derived from the extract column, but you can't update these special tables unfortunately.

What you can do is look at the inserted table and pluck out into from the ID you want and stuff it into the other column. There are two ways to do this. You can use a regular Insert (After) trigger or you can use an Insert (Instead Of) trigger. Each of these behave differently. With an After trigger (the default) you can issue and update statement inside the trigger. Unless you ROLLBACK a transaction in a trigger the data is already posted to the underlying table. So, if you create a trigger like this:

CREATE TRIGGER [testtrg] ON [dbo].[Test]
FOR INSERT
AS

Update Tst
Set IDExtract=Substring(ins.ID,2,1)
From Inserted ins
JOIN Test tst
ON (ins.ID=tst.ID) -- Note that ID should be a unique primary key

Or, you can use an Instead of trigger. With this type of trigger you are responsible for posting the data yourself. The nice thing about this is that you can do it all in one step. You won't have to issue an update.
Have a look:

CREATE TRIGGER [testtrg] ON [dbo].[Test]
INSTEAD OF INSERT
AS

Declare @lExtractPosition Int
Declare @lCharactersToExtract Int
Set @lExtractPosition=2 -- 2 will be the character position the extract will
Set @lCharactersToExtract=1 -- We only want to extract 1 character
begin

-- Insert the data into the table from the inserted table
Insert Into Test (ID,IDExtract)
Select ins.ID,
CASE
-- Check the size of incoming ID
When DataLength(ins.ID) >= (@lExtractPosition+@lCharactersToExtract)
-- If it's big enough extract it!
Then SubString(ins.ID,@lExtractPosition,@lCharactersToE xtract)
-- Otherwise return NULL (or you can use any default
Else NULL
END
From Inserted ins
END


Hope this helps.








------------
tc at 6/22/01 4:16:03 PM

I think a trigger is the way to go on this but let me try to explain the confusion. I have a table with an id field. Based upon this ID field I need to have a character stripped out of the ID and placed in another column on the same table/same record(Sorry, because of the development env I can't use a computer column). The question is how to do this with an insert trigger. My understanding is the trigger will fire on the insert, but the record isn't there yet. Is there a way to handle this? My code below does not error but also does not update?

create TRIGGER test_Ins_tr
ON table1
FOR update
AS
DECLARE @oid char(1)
declare @actid char(10)
SELECT @oid = SUBSTRING(right(col1,2),1,1), @actid = col1
FROM inserted
update table1
set col2 = @oid where table1.col1 = @actid