Trigger For INSERT update field required

[Danster]

I have a trigger which needs to update a field into my table only for an INSERT.

My trigger reads thus...
CREATE TRIGGER MyTrigger ON MyTable
FOR INSERT
AS
DECLARE @SerialNo varchar (20)

--Get the 6 char date
SET @SerialNo = CONVERT(char(20), GETDATE(),112)
SET @SerialNo = RIGHT(RTRIM(@SerialNo), 6)

UPDATE MyTable SET MyTable.field = @SerialNo
FROM Inserted
WHERE MyTable.RecID = Inserted.RecID
and len(Inserted.field) = 0


If this was an UPDATE trigger, this code works great, but of course during the INSERT, the table record doesn't exits yet so I can't use
WHERE MyTable.RecID = Inserted.RecID

I need to update the inserted.field, but inserted is a strictly a readonly affair, so how can I update the field as a record is INSERTed?

cheers

 

[mrdenny]

If you do not have cascadding triggers on your server you could change it to an INSTEAD OF trigger and update the inserted table (which I think you can do) or copy the table to a temp table, update the field to the value then re-write what ever if in the temp table to the production table.

Not sure if it would work or not, but it might.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Danster]

Thx Denny,
I definitely cannot update the inserted table, SQL warns me with an error
"the logical tables INSERTED and DELETED cannot be updated"

I can't write to the production table because there is no record yet to update. I need to set the value during the INSERT event.

cheers

Danster

 

[bdiaz]

Your query should work for update and insert, as during the insert the record exists both in the real and inserted tables.

 

[unclejimbob]

change

and len(Inserted.field) = 0

to

and len(Inserted.MyField) = 0 or Inserted.MyField is null