T-SQL how to modify the value before insert

Question

I find that there are only after and instead of triggers in sql server. And it is illegal to modify the values in the inserted pesudo table. Then my problem occurs: If I want to check the data which is going to be inserted into my table, and when the data violates my constraints I should modify these values to default values, how to do it ? How about updateing the values after inserted ? However, if there's no primary key or colum which is unique in my table, how can I locate the row just inserted and then update it ?

Solution

Basically, with an INSTEAD OF INSERT trigger, you can achieve what you're looking for - just read out the data from the INSERTED pseudo table, modify it, and insert it into the table

So your trigger would look something like this:

CREATE TRIGGER YourTrigger ON dbo.YourTable    
INSTEAD OF INSERT
AS
    SET NOCOUNT ON

    -- do the INSERT based on the INSERTED pseudo table, modify data as needed
    INSERT INTO dbo.YourTable(Col1, Col2, ....., ColN)
      SELECT 
          Col1, 2 * Col2, ....., N * ColN
      FROM 
          INSERTED

Of course, you could also add e.g. checks in the form of WHERE clause to that SELECT .... FROM INSERTED statement to e.g. ignore certain rows - the possibilities are endless!